newbie - please help with create db code

[baltar]

I'm just starting out with mysqli and php.  Actually I more or less know how to create/connect/populate mysqli databases through php.  But I can only do so by using a separate php page for each objective.  Anyway, I learning with the kindle version of Joy of Php.  The code though isn't very good...I had to edit it just to make it conform to php/mysqli syntax.  Programmer's Notebook was a big help actually.

 

Anyway, I just wanted to point out my experience level.  Below is the code, I haven't run it through wamp (Phpmyadmi) yet, only because it probably won't run and I'll be crushed...side note: it took me a while to figure out that tables and databases require the use ` and not '.  The book doesn't even mention the difference!!!!

 

Anyway, can someone tell me  if this edited code is viable?  I'm concerned about line 4: $mysqli....  since this is wamp, and I log into phpmyadmin via -u root -p ,  -p NULL in the code is correct yes?

<?php 
/*Joy of PHP sample code*/
 
$mysqli = new mysqli('localhost', 'root', NULL );
 
if (mysqli_connect_error()) 
{
die('Could not connect: ' . mysqli_connect_error());
}
echo 'Connected successfully to mySQL.';
 
/* Create table doesn't return a resultset */
 
if ($mysqli->query("CREATE DATABASE Cars") === TRUE) 
{
echo "Database Cars created";
}
else
{
echo "Error creating Cars database: " . $mysqli->error."<br>";
}

$mysqli->select_db("Cars");
Echo ("Selected the Cars database");

$query= " CREATE TABLE INVENTORY (VIN varchar(17) PRIMARY KEY, YEAR INT, Make varch(50), Model varchar(100))";
//echo "<p>*****</p>";
//echo $query;
//echo "<p>*****</p>";

if ($mysqli->query ($query) === TRUE)
{
echo "<p>Database table 'INVENTORY' CREATED</p>";
}
else
{
echo "<p> ERROR: </p>" . mysqli_error($mysqli);
}

$query = "INSERT INTO `cars` . `inventory` (`VIN`, `YEAR`, `Make`, `Model`) VALUES '5FNYF4H91CB054036',  '2012', 'Honda', Pilot')";


if ($mysqli->query($query) === TRUE)
{
echo "<p>Honda Pilot inserted into inventory table. </p>";
}
else
{
echo "<p>Error inserting Honda Pilot:</p>" . mysqli_error($mysqli);
echo "<p>*****</p>";
echo $query;
echo "<p>*****</p>";
}

//Insert a Dodge Durango

$query= "INSERT INTO `cars` . `inventory` ('VIN', 'YEAR', 'Make', 'Model') VALUES ('LAKSDFJ234LASKRF2', '2009', 'Dodge', 'Durango')";

if ($mysqli->query($query) ===TRUE)
{
echo "<p>Dodge Durango inserted into inventory table </p>";
}
else
{
echo "<p>Error inserting Dodge: </p>" . mysqli_error($mysqli);
echo "<p>*****</p>";
echo $query;
echo "<p>*****</p>";
}

$mysqli->close();
?>

Thank you,  I apologize if I seem critical of the book but it really skips over A LOT of stuff that newbies don't necessarily know (example: -u something -p = username  something and password is blank)

Edited May 17, 2014 by baltar

[baltar]

the board won't let me re-edit.  It appears the main problems (among others?)  were a ( was missing and also I had used '  instead of `. 

 

Anyone know if there is a reason why db_names and tables can't handle an apostrophe but must have `.  I mean apostrophes work fine for encapsulating values...

[Adam]

It's not that MySQL can't "handle" them. Single and double quotes are used to indicate a string, where as back-ticks indicate the name of a database, table, column name, etc. It's like asking why an ampersand can't be used instead of an equals sign when comparing values.

 

They're normally not actually required either by the way. Only if the `name` in question clashes with a reserved word, then they're required so that MySQL knows it's the name of something.

[Barand]

They're normally not actually required either by the way. Only if the `name` in question clashes with a reserved word, then they're required so that MySQL knows it's the name of something.

 

... or if the identifier contains spaces or other special characters. EG

SELECT SUM(price) as `Total Sales` ...