Simple MySQL / PHP syntax

[Ducky1]

Hey guys. Apologies in advance if this is in the wrong section, but it seems to be more based on a MySQL error than PHP. Im basically trying to learn SQL, and attempting to write my first script in PHP. Im trying to get it to connect, make a table, and insert one set of values, then print it out. I added Echo's to see where it wasn't working, but since none came up, im suspecting that some PHP syntax is wrong. Thanks in advance.

 

<?php

Echo ("Connecting...");

mysql_connect("*********", "*******", "***");

Echo ("Connect");

 

mysql_select_db("**********");

Echo ("select db");

 

$data = mysql_query("CREATE TABLE people (name VARCHAR(30),level name VARCHAR(30)); INSERT INTO people VALUES ( "Peter","40" )");

Echo ("Insert names");

 

$data = mysql_query("SELECT * FROM people");

//$info = mysql_fetch_array( $data );

Echo ($data);

?>

[Floydian]

I see two things, number one, you can only pass one query at a time in a mysql_query() function call. Split the create and insert queries into two function calls.

 

Number two, the insert query has double quotes in it which is breaking the string, since your query string is quoted using double quotes. Try replacing the quotes in the query with single quotes, and leaving the quotes surrounding the string with double quotes.

 

A better test for the connection would be:

 

$conn = mysql_connect("*********", "*******", "***");
if (!$conn ) { echo "Could not connect to the database."; }

$did_we_find_a_db_to_use = mysql_select_db("**********");
if (!$did_we_find_a_db_to_use) { echo "Invalid database name."; }

 

[Ducky1]

I see two things, number one, you can only pass one query at a time in a mysql_query() function call. Split the create and insert queries into two function calls.

 

Number two, the insert query has double quotes in it which is breaking the string, since your query string is quoted using double quotes. Try replacing the quotes in the query with single quotes, and leaving the quotes surrounding the string with double quotes.

 

A better test for the connection would be:

 

$conn = mysql_connect("*********", "*******", "***");
if (!$conn ) { echo "Could not connect to the database."; }

$did_we_find_a_db_to_use = mysql_select_db("**********");
if (!$did_we_find_a_db_to_use) { echo "Invalid database name."; }

 

Thanks for the help. So something like...?

 

<?php

$conn = mysql_connect("*********", "*******", "***");

if (!$conn ) { echo "Could not connect to the database."; }

 

$did_we_find_a_db_to_use = mysql_select_db("**********");

if (!$did_we_find_a_db_to_use) { echo "Invalid database name."; }

 

$data = mysql_query("CREATE TABLE people (name VARCHAR(30),level name VARCHAR(30)); INSERT INTO people VALUES ( 'Peter','40' )");

Echo ("Insert names");

 

$data = mysql_query("SELECT * FROM people");

//$info = mysql_fetch_array( $data );

Echo ($data);

?>

[Ducky1]

Ive got this so far, but it returns the following...

 

"Insert names

 

Data:Resource id #2

 

Info:Array"

 

Can you explain to a newb like me what this is? How to use it, or how to get the results im looking for?

 

<?php
$conn = mysql_connect("mysql4.freehostia.com", "rovstr_rrr", "rrr");
if (!$conn ) 
{ 
echo "Could not connect to the database."; 
}
$did_we_find_a_db_to_use = mysql_select_db("rovstr_rrr");
if (!$did_we_find_a_db_to_use) 
{ 
echo "Invalid database name."; 
}
$data = mysql_query("CREATE TABLE people (name VARCHAR(30),level VARCHAR(30))"); 
$data = mysql_query("INSERT INTO people VALUES ('Peter','40')");
Echo ("Insert names");

$data = mysql_query("SELECT * FROM people");
$info = mysql_fetch_array($data);
echo("<p> Data:");
Echo ($data);
echo("<p> Info:");
Echo ($info);
?> 

[suma237]

$data = mysql_query("SELECT * FROM people");

while($info = mysql_fetch_array($data))

{

echo $info['name'];

echo "<br>" $info['level'];

}

[Floydian]

<?php

$conn = mysql_connect("xxxxxxx", "xxxxxxx", "xxxxxxx");
if (!$conn )  { 
echo "Could not connect to the database."; 
}
$check = mysql_select_db("xxxxxxx", $conn);
if (!$check)  { 
echo "Invalid database name."; 
}

// You've already created the table. Most of the time, you'll create tables outside of
// your php scripts. (most of the time...)
// $data = mysql_query("CREATE TABLE people (name VARCHAR(30),level VARCHAR(30))"); 

// You don't have a primary key, so inserting the same exact values over and over again is fine here
mysql_query("INSERT INTO people VALUES ('Peter','40')", $conn);

// the mysql_affected_rows function will tell you if the insert was succesful
if (mysql_affected_rows($conn) < 1) {
echo "Insert query failed.";
}


$data = mysql_query("SELECT name, level FROM people", $conn);

// Your SELECT query will likely return more than one ROW from the db.
// I'm using LIST() which takes an array (which is what you get when using mysql_fetch_array())
// and stores the array values in variables. I set the result type to MYSQL_NUM
// because LIST() works with numeric arrays.
while(list($name, $level) = mysql_fetch_array($data, MYSQL_NUM)) {
echo "Name: $name and Level: $level<br>\n";
}

 

 

Hope that helps...

[Ducky1]

Sorry if i ignored your last post, i only saw it after i posted this, and didnt have time to re-edit it.

 

OK. Im trying to make something like a medicine guide. I got it working, but as soon as I added more values it stops returning a table. Probably something simple.. yet again.

 

<?php
$conn = mysql_connect("****", "*****", "****");
if (!$conn ) 
{ 
echo "Could not connect to the database."; 
}
$did_we_find_a_db_to_use = mysql_select_db("******");
if (!$did_we_find_a_db_to_use) 
{ 
echo "Invalid database name."; 
}
$data = mysql_query("CREATE TABLE medicine (name VARCHAR(30),dosage VARCHAR(30),desc VARCHAR(30),when VARCHAR(30))"); 
$data = mysql_query("INSERT INTO medicine VALUES ('Pennicillin','One Tablet','Green round','After dinner')");
//$data = mysql_query("DELETE * FROM people");
Echo ("Medication table returned via SQL script:");

$data = mysql_query("SELECT * FROM medicine");
Print "<table border cellpadding=3>";
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<th>Name:</th> <td>".$info['name'] . "</td> ";
Print "<th>Dosage:</th> <td>".$info['dosage'] . " </td>";
Print "<th>Description:</th> <td>".$info['desc'] . "</td> ";
Print "<th>When:</th> <td>".$info['when'] . "</td></tr>";
}
Print "</table>";
echo("This table is purely for test purposes");
?>

[Floydian]

I am not seeing anything wrong there. I would however recommend commenting out the table creation query.

 

$data = mysql_query("CREATE TABLE medicine (name VARCHAR(30),dosage VARCHAR(30),desc VARCHAR(30),when VARCHAR(30))"); 

 

(I'm assuming you've created the table already...)