On Oct 17, 2010, at 1:22 PM, Ethan Rosenberg wrote:
At 01:41 AM 10/17/2010, Tommy Pham wrote:
> I cannot get the following to work. In my Firefox [Iceweasel]
browser, I
> enter the following URL: [w/ the http]
Whenever you get a blank screen running a php application, the
place
to look is the http server's error_log. This is frequently found
in / var/log/httpd/error_log or /var/log/apache2/error_log. (If
your
system is hosted someplace else, it could very easily be in a
different place). Typically you need root permission to read this
file. Tail the file after you run your PHP script to see the most
recent errors.
> The code contained in the file CreateNew.php is:
>
> /*
> * Create Database test22
> */
> <html><body>
> <?php
> $cxn = mysqli_connect("$host",$user,$password);
Better to use the OO approach:
$cxn = new mysqli($host, $user, $password);
> echo "Create database test22;"
Instead of echo statements (which would just echo the contents to
the output, i.e., your browser, you want to assign them to a
variable, such as:
$sql = "create database test22; use test22";
Then you need to execute the sql statement:
$res = $cxn->query($sql);
if (!$res) {
die("Could not create database test22: " .
$cxn- >error());
}
> echo "Create table Names2
$sql = "create table Names2
> (
> RecordNum Int(11) Primary Key Not null default=10000
auto_increment,
> FirstName varchar(10),
> LastName varchar(10),
> Height decimal(4,1),
> Weight0 decimal(4,1),
> BMI decimal(3,1)
> Date0 date
> );"
; // to close off the php statement
$res = $cxn->query($sql);
if (!$res) {
die("Could not create table Names2: " . $cxn- >error());
}
>
> echo" Create table Visit2
$sql = "create table Visit2
> (
> Indx Int(7) Primary Key Not null auto_increment,
> Weight decimal(4,1) not null,
> StudyDate date not null,
> RecordNum Int(11)
> );"
; // again, to close off the php statement
$res = $cxn->query($sql);
if (!$res) {
die("Could not create table Visit2: " . $cxn- >error());
}
>
> $sql= "SHOW DATABASES";
This doesn't work in a programmatic setting.
Terminate the database connection:
$cxn->close();
> ?>
> </body></html>
> I would also like to be able to add data to a table, using
PHP,
which I
can do
> in MySQL as:
> load data infile '/home/ethan/Databases/tester21.dat.' replace
into table
> Names fields escaped by '\\' terminated by '\t' lines
terminated by '\n'
;
That's a specific feature of the mysql program. You'd have to
write
something in php to be able to parse the file and insert the data.
There are examples all over the net. Then you would need to set up
sql insert or replace statements to actually get the data into the
data base using mysqli::query. There are numerous examples of this
as well.
Here's one example:
<?php
$host = "localhost";
$user = "root";
$pwd = "rootpassword";
$db = "test22";
$table = "table_to_insert_into";
$cxn = new mysql($host, $user, $pwd, $db);
$filename = "tab-delimited.txt";
$contents = file($filename); // returns the contents of
the file
into an array, one line of file per array
$columns = explode("\t", $contents[0]); // get the column
names
from the first line of the file
$sql = "insert into $table set ";
for ($i=1; $i<count($contents) ; $i++) {
$data = explode("\t", $contents[$i]);
$j = 0;
foreach ($columns as $column) {
$insertdata[] = "$column='" .
$cxn- >real_escape_string($data[$j+ +]) . "'"; // this assumes the
column names in the tsv file match
the column names in your data base table exactly. It also assumes
that all your data are strings, not numerics.
}
$sql .= implode(",",$insertdata);
$res = $cxn->query($sql);
if (!res) die ("Error inserting data: " .
$cxn- >error());
}
?>
<html><head><title>Imported data</title></head>
<body>
<p>Data just imported:</p>
<table border="1" cellpadding="2px" cellspacing="2px">
<thead>
<tr style="color: white; background-color: black; text-align:
center">
<?
$res = $cxn->query("select * from $table limit 1"); //
get one row
from table for generating column names
if (!res) die ("Query failed for table $table: " .
$cxn- >error());
$row = $res->fetch_assoc();
foreach ($row as $column => $value) {
echo "<th>" . $column . "</th>";
}
?>
</tr>
</thead>
<tbody>
<?
$res = $cxn->query("select * from $table");
if (!res) die ("Query failed for table $table: " .
$cxn- >error());
while ($row = $res->fetch_assoc()) {
echo "<tr>";
foreach ($row as $column => $value) {
echo "<td>" . $value . "</td>";
}
echo "</tr>\n";
}
?>
</tbody>
</table>
</body>
</html>
As I stated, I am a newbie.
1] I am trying to shorten the learning curve by asking some
questions, which I understand are probably trivial. A whole
MySQLi
list of functions at this point is to much for me. I have to
break
the problem into manageable parts.
Important, most used mysqli functions:
Creating the database connection: $cxn = new mysqli( host, user,
password, database ); (mysqli::__construct function)
Submitting queries: $result = $cxn->query( sql ); (mysqli::query
function)
Working with results: $row = $result->fetch_assoc(); -- returns an
associative array of a sql select statement result, subsequent
calls
return the next row in the result until the results are exhausted.
Syntax errors: $cxn->error() -- reports the mysql error of the
last
query.
These will get you started quite well.
2] It has been my experience that using a GUI does not teach the
whole subject. Linux, which is the OS I use cannot be run from a
GUI.
In the code being discussed, I wish to create a database and add
two tables. I also note a MySQL statement that can be used to
add
data to an existing table, and wish to be able to execute this
statement using PHP.
mysql(1) is an interactive program that does a lot of things to
help
you interact with mysql databases. It's functions are not all
duplicated in the programmatic interface to mysql(i) -- i.e. you
have to write them yourself.
So, therefore......
Let us try to answer the following two(2) questions:
a] What changes [other than moving the simicolons] have to be
made
to correct the code.
See above
b] What books can you suggest to help w/ MySQL and PHP? I
already
have the SQL, MySQL & PHP, and HTML books in the ..... for
Dummies
series. I need something with a little more depth and detail.
Personally, Dummies books don't provide the necessary
information to
actually become proficient at any particular topic.
My personal favourite books are in the O'Reilly catalog, although
some are dated:
- Head First PHP and MySql: http://oreilly.com/catalog/9780596006303/
- PHP Cookbook: http://oreilly.com/catalog/9780596101015/
- Learning PHP, MySQL, and JavaScript:
http://oreilly.com/catalog/9780596157135/
- MySQL Cookbook (oreilly.com is currently returning a server
error
on this title, unfortunately)
Tamara
