[FIXED] What is my SQL missing?

Issue

I'm learning about PHP and MySQL. I have a lab that I'm working on where I have created a database (using the terminal in Ubuntu through a Virtual Machine) and I am trying to access this database using a PHP file.

I have created 1 database (demo) which holds 2 tables (demo_table, lab4) using this script

CREATE TABLE `lab4` (
 id int(11) NOT NULL AUTO_INCREMENT,
 first_name varchar(100) DEFAULT NULL,
 last_name varchar(100) DEFAULT NULL,
 email varchar(128) DEFAULT NULL,
 phone varchar(25) DEFAULT NULL,
 PRIMARY KEY (id)
);

I can see through the terminal that they have been created the way I want, etc.

I have a php file (provided to me) in the appropriate directory (/var/www/html) which runs correctly, producing the output it is supposed to. Here is that file:

note: i have redacted my passwords!

<?php

function listRecords($dbc, $dbtable){
    $qry = "SELECT * FROM ".$dbtable.";";
    if($result = $dbc->query($qry)){
            if ($result->num_rows > 0){
                while ($row = $result->fetch_assoc()){
                        foreach($row as $k=>$v){
                Print $k."  ".$v."\t";
            }
            print "\n";
                }
        } else {
                echo "Nothing to output\n";
        }
        $result->free();
} else {
        die("Error running database query for categories\n  $qry \n");
}
}

$db_conn = new mysqli('localhost', 'l*******r', '*******', 'demo');
if ($db_conn->connect_errno) {
die ("Could not connect to database server".$db_host."\n Error: ".$db_conn-
>connect_errno ."\n Report: ".$db_conn->connect_error."\n");
}

$qry = "INSERT INTO demo_table values('1', 'First User', 
'first@somewhare.com');";
$db_conn->query($qry);
$qry = "INSERT INTO demo_table values('2', 'Second User', 
'second@somewhare.com');";
$db_conn->query($qry);
$qry = "INSERT INTO demo_table values('3', 'Third User', 
'third@somewhare.com');";
$db_conn->query($qry);

listRecords($db_conn, 'demo_table');


print "\nDeleteing record 1\n";
$db_conn->query("DELETE FROM demo_table WHERE id='1';");

listRecords($db_conn, 'demo_table');

print "\nModifyingrecord 3\n";

$db_conn->query("UPDATE demo_table SET email='THIRD@newdomain.com' WHERE 
id='3';");

listRecords($db_conn, 'demo_table');

$db_conn->close();

?>

This was provided to me, and I have not changed it in any way from the way my instructor gave it to me. This confirms that i at least created the demo_table correctly.

I was tasked with creating another php file which will perform some other similar tasks (basic sql stuff). So, I have copied my instructor's listRecords function and then written a bunch of sql queries myself. Here is my code:

<?php
//A.


//function to be used in D. below
function listRecords($dbc, $dbtable)
{
$qry = "SELECT * FROM ".$dbtable.";";

if($result = $dbc->query($qry))
{
        if ($result->num_rows > 0)
        {
                while ($row = $result->fetch_assoc())
                {
                        foreach($row as $k=>$v)
                        {
                           Print $k."  ".$v."\t";
                        }
                        print "\n";
                }
        }  else 
        {
            echo "Nothing to output\n";
        }
        $result->free();
} else 
{
    die("Error running database query for categories\n  $qry \n");
}
}

//B.
//create a connection object
$db_conn = new mysqli('localhost', '*********', '*******', 'demo');

//IF CONNECTION FAILED: display errors
if ($db_conn->connect_errno)
{
die ("Could not connect to database server".$db_host."\n Error: ".$db_conn-
>connect_errno."\n Report: ".$db_conn->connect_error."\n");
}

//C.
//IF CONNECTION SUCCEEDED: perform SQL
//single variable name can be reused
$qry = "INSERT INTO lab4 values('First', 'User', 'first@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Second', 'User', 'second@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Third', 'User', 'third@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Fourth', 'User', 'fourth@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Fifth', 'User', 'fifth@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Sixth', 'User', 'sixth@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Seventh', 'User', 'seventh@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Eighth', 'User', 'eighth@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values('Ninth', 'User', 'ninth@somewhare.com', '1111111');";
$db_conn->query($qry);
$qry = "INSERT INTO lab4 values(Tenth', 'User', 'tenth@somewhare.com', '1111111');";
$db_conn->query($qry);

//D.
//display records
listRecords($db_conn, 'lab4');

//E.
//delete rows with IDs 2 and 4
$db_conn->query("DELETE from lab4 WHERE id=2");
$db_conn->query("DELETE from lab4 WHERE id=4");


//F.
//display all records after deletion in step E.
listRecords($db_conn, 'lab4');

//G.
//change email of 5th record
$db_conn->query("UPDATE lab4 SET email='newemail@somewhare.com' WHERE         id=5");

//H.
//change first name of 8th record
$db_conn->query("UPDATE lab4 SET first_name='Newname' WHERE id=8");

//I.
//change last name of 10th record
$db_conn->query("UPDATE lab4 SET last_name='Lastname' WHERE id=10");

//K.
//close the database connection
$db_conn->close();
?>

So I have changed some of the formatting, and changed every instance of "demo_table" to "lab4".

When I run my php (lab4.php, located in same directory as the previous code) I get the message Nothing to output twice. My interpretation of this is that my table is working correctly and my listRecords is working correctly (as it is not giving me the error message associated with a failed connection).

My best guess is that it is a problem with my SQL queries.

Solution

Either provide the value for the ID column, or allow the system to generate one for you ( the auto_increment).

So either...

$qry = "INSERT INTO lab4 values(1, 'First', 'User', 'first@somewhare.com', '1111111')";

Or preferably...

$qry = "INSERT INTO lab4 ( first_name, last_name, email, phone )
           values ('First', 'User', 'first@somewhare.com', '1111111');";

This will generate the id for you.

Answered By - Nigel Ren

Answer Checked By - Marilyn (PHPFixing Volunteer)