PHP Help & MYSQL

[Ch0cu3r]

Seems like the prepared query is failing.

 

Before line 94 add the following

if(!$stmt)
{
     trigger_error('Unable to prepare query: ' . $mysqli->error);
}

What error is shown this time?

[G3NERALCHRIS]

Seems like the prepared query is failing.

 

Before line 94 add the following

if(!$stmt)
{
     trigger_error('Unable to prepare query: ' . $mysqli->error);
}

What error is shown this time?

where about do you want me to put this line in same if statement for example. 
    // only run the query if the data is valid
    if($isValidData)
    {
        // use a prepared statement for inserting data into the table
        $stmt = $mysqli->prepare("INSERT INTO $tbl_name VALUES (?, ?, ?, ?)");
    if(!$stmt)
    {
     trigger_error('Unable to prepare query: ' . $mysqli->error);
    }
        // bind the values to the query
        $stmt->bind_param('ssss', $name,
                                  $email,
                                  $phone,
                                  $comment);

[G3NERALCHRIS]

where about do you want me to put this line in same if statement for example. 
    // only run the query if the data is valid
    if($isValidData)
    {
        // use a prepared statement for inserting data into the table
        $stmt = $mysqli->prepare("INSERT INTO $tbl_name VALUES (?, ?, ?, ?)");
    if(!$stmt)
    {
     trigger_error('Unable to prepare query: ' . $mysqli->error);
    }
        // bind the values to the query
        $stmt->bind_param('ssss', $name,
                                  $email,
                                  $phone,
                                  $comment);

I get these errors using the above

Notice: Unable to prepare query: No database selected in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 94 - trigger_error('Unable to prepare query: ' . $mysqli->error);

 

Fatal error: Call to a member function bind_param() on a non-object in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 97 $stmt->bind_param('ssss', $name,

 

Those are the two errors.

[Ch0cu3r]

You have not connected to your database. Make sure you have filled in your database credentials on these four lines at the top of your script

$host="localhost";         // Host name
$username="root";         // Mysql username
$password="";         // Mysql password
$db_name="";         // Database name
$tbl_name="contactme";     // Table name

[G3NERALCHRIS]

 

You have not connected to your database. Make sure you have filled in your database credentials on these four lines at the top of your script

$host="localhost";         // Host name
$username="root";         // Mysql username
$password="";         // Mysql password
$db_name="";         // Database name
$tbl_name="contactme";     // Table name

I have filled that had that previously i only took a small chunk of code for that example.

Full code below.

<?php

$host="localhost";         // Host name
$username="root";         // Mysql username
$password="";         // Mysql password
$db_name="";         // Database name
$tbl_name="contactme";     // Table name

// using mysqli see http://php.net/mysqli for documentation
$mysqli = new mysqli("$host", "$username", "$password", "$db_name");

// if a post request has been made
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    // Simple data validation checking to make sure name, email, phone and comment exist.

    // You should do futher checks here, ie making sure
    //      - a name has been entered - it is not just an empty string
    //      - email address entered is a valid address
    //      - the phone number is a valid phone number
    //      - a comment was entered - it is not just an empty string

        // set to true for now. This will be set to false if the data does not validate
        $isValidData = true;

        // used to log errors for fields that do not validate
        $errors = array();

        // check to make sure name exists in POST
if(isset($_POST['author']))
{
$name = $_POST['author'];
}
else
{
// add error to $errors array
$errors[] = 'Please enter your name';
}

// check to make sure email exists in POST
if(isset($_POST['email']))
{
$email = $_POST['email'];
}
else
{
// add error to $errors array
$errors[] = 'Please enter your email address';
}

// check to make sure phone exists in POST
if(isset($_POST['phone']))
{
$phone = $_POST['phone'];
}
else
{
// add error to $errors array
$errors[] = 'Please enter your phone number';
}

// check to make sure comment exists in POST
if(isset($_POST['text']))
{
$comment = $_POST['text'];
}
else
{
// add error to $errors array
$errors[] = 'Please enter your comment';
}
// end validation
    // check to make sure data did validate - nothing  should be in $errors.
    if(count($errors) != 0)
    {
        // set to false
        $isValidData = false;

        // display the error messages for fields that do not validate
        echo 'Please correct the following: <ul><li>' . implode('</li></li>', $errors) . '</li></ul>';

        // dispay the contact form again here
        include 'contact.html';
        exit;
    }

    // only run the query if the data is valid
    if($isValidData)
    {
        // use a prepared statement for inserting data into the table
        $stmt = $mysqli->prepare("INSERT INTO $tbl_name VALUES (?, ?, ?, ?)");
    if(!$stmt)
    {
     trigger_error('Unable to prepare query: ' . $mysqli->error);
    }
        // bind the values to the query
        $stmt->bind_param('ssss', $name,
                                  $email,
                                  $phone,
                                  $comment);

        // execute prepared statement
        $result = $stmt->execute();

        // make sure the statement did not return an error
        if(!$result)
        {
            // trigger error.
            trigger_error("Unable to insert data into $tbl_name in the database. " . $mysqli->error);
        }

        // check to see if the insert query did affect the table.
        if($result && $mysqli->affected_rows)
        {
            echo "Successful<BR>";
            echo "<a href='view-contactme.php'>View Contact Me</a>"; // link to view contact me page
        }
    }
}

[Ch0cu3r]

 

 

I have filled that had that previously i only took a small chunk of code for that example.

What? I don't understand what you just said.

[G3NERALCHRIS]

What? I don't understand what you just said.

 

Sorry I have put the code in that you previously gave me but get two errors. As stated above not sure what is wrong.  I have the connection to the database from the start.

[Ch0cu3r]

Those errors you are getting are these. Correct?

 

 

Notice: Unable to prepare query: No database selected in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 94 - trigger_error('Unable to prepare query: ' . $mysqli->error);

Fatal error: Call to a member function bind_param() on a non-object in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 97 $stmt->bind_param('ssss', $name,

They are all caused by the first error. I have highlighted the cause of that error in red.

 

You are getting that error because PHP has connected to MySQL but it has not selected a database. The code was not unable to initiate the prepare statement because no database has been selected.

 

This is why I said you need to fill in your database credentials for the five variables I showed you in post #29. You understand what I mean by that?

[G3NERALCHRIS]

Those errors you are getting are these. Correct?

They are all caused by the first error. I have highlighted the cause of that error in red.

 

You are getting that error because PHP has connected to MySQL but it has not selected a database. The code was not unable to initiate the prepare statement because no database has been selected.

 

This is why I said you need to fill in your database credentials for the five variables I showed you in post #29. You understand what I mean by that?

Thank you for your help. Do you any suggestion on how you can delete a entire row from view-contactme.php?

<?php
$host= "localhost";
$user = "root";
$passwd = "";
$database = "test";
$tbl_name = "contactme";

mysql_connect ($host, $user, $passwd) or  die("cannot connect server ");
mysql_select_db( $database) or die("cannot select DB");

$result=mysql_query("SELECT * FROM $tbl_name");

while($rows = mysql_fetch_array($result)){
echo $rows['name'] .  " "  .$rows['email'] .  " " .  $rows['phoneno']  .  " " .  $rows['comments'] . "<br>";
}
mysql_close();     //close database
?>

So basically I was thinking something like. Link to a new php called delete.php and have a submit button on the bottom of the view-contactme.php. Example of delete is below.

<?php
$host="localhost";         // Host name
$username="root";         // Mysql username
$password="";         // Mysql password
$db_name="test";         // Database name
$tbl_name="contactme";     // Table name

mysql_connect ("$host", "$username", "$password") or die ("cannot connect server ");
mysql_select_db ("$db_name") or die ("cannot select DB");

// sql to delete a record
$sql = "DELETE FROM guestbook WHERE name='chris'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Edited November 23, 2014 by G3NERALCHRIS

[Ch0cu3r]

To delete a record you need some way of identifying that record. You could use the name of the contact but people do not have unique names. There could be multiple people called chris stored in your table and so any one with the same name will be deleted from your table.

 

Instead what you should do is alter your contactme table and add an id field which is set to auto increment. Doing this will ensure each row has a unique id when a new record is inserted into the table. It is this is id you'd use to identify each record. To do this you can run this sql query

ALTER TABLE  `contactme` ADD  `id` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);

Now if you look at the data in your table you'd should see each row now has a unique number assigned to them.

 

Now that we have the id column we can use this to create the delete link. To do so in view-contactme.php change this line

echo $rows['name'] .  " "  .$rows['email'] .  " " .  $rows['phoneno']  .  " " .  $rows['comments'] . "<br>";

to

echo $rows['name'] .  " "  .$rows['email'] .  " " .  $rows['phoneno']  .  " " .  $rows['comments'] . "| <a href=\"delete-contact.php?id={$row['id']}\">Delete</a><br>"; // added delete link. The record id is being passed in the url

You should now see a delete link next to each row. Clicking the link will pass that rows id to delete-contact.php in the url

 

 

In delete-contact.php you'd use  $_GET['id']  to retrieve the record id being passed. You'd run a query to delete the record where the id matches $_GET['id']. Example code using mysqli prepared query

// connect to mysql using MySQLi
$mysqli = new mysqli($host, $username, $password, $db_name);

// check to make sure the id exists and it is a number
if(isset($_GET['id']) && ctype_digit($_GET['id']))
{
    // prepare the delete query
    $stmt = $mysqli->prepare("DELETE FROM $tbl_name WHERE id= ?");

    // Issue an error if the prepared statement failed

    if(!$stmt)

    {

trigger_error('Unable to prepare query: ' . $mysqli->error);

    }

// bind the record id to the query
$stmt->bind_param('i', intval($_GET['id']));

// execute the prepared query
$result = $stmt->execute();

// if the query did not execute trigger an error message
if(!$result)
{
trigger_error("Unable to delete record #{$_GET['id']} from $tbl_name - " . $mysqli->error);
}

// check to make sure the query did affect the table.
if($result && $mysqli->affected_rows)
{
echo "Record deleted successfully";
}
}

 

Now because we added the id field to the table we need to go back and alter the code slightly in add-contact.php. Find the prepared query in add-contact.php and change it to this

// specify the columns we are inserting the data into
$stmt = $mysqli->prepare("INSERT INTO $tbl_name (name, email, phone, comment) VALUES (?, ?, ?, ?)");

Now your challenge is to alter the code in view-contact.php over to mysqli. As I said earlier the use of the mysql_ functions are deprecated meaning they are no longer supported and could be removed from future versions of PHP.

[G3NERALCHRIS]

To delete a record you need some way of identifying that record. You could use the name of the contact but people do not have unique names. There could be multiple people called chris stored in your table and so any one with the same name will be deleted from your table.

 

Instead what you should do is alter your contactme table and add an id field which is set to auto increment. Doing this will ensure each row has a unique id when a new record is inserted into the table. It is this is id you'd use to identify each record. To do this you can run this sql query

ALTER TABLE  `contactme` ADD  `id` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);

Now if you look at the data in your table you'd should see each row now has a unique number assigned to them.

 

Now that we have the id column we can use this to create the delete link. To do so in view-contactme.php change this line

echo $rows['name'] .  " "  .$rows['email'] .  " " .  $rows['phoneno']  .  " " .  $rows['comments'] . "<br>";

to

echo $rows['name'] .  " "  .$rows['email'] .  " " .  $rows['phoneno']  .  " " .  $rows['comments'] . "| <a href=\"delete-contact.php?id={$row['id']}\">Delete</a><br>"; // added delete link. The record id is being passed in the url

You should now see a delete link next to each row. Clicking the link will pass that rows id to delete-contact.php in the url

 

 

In delete-contact.php you'd use  $_GET['id']  to retrieve the record id being passed. You'd run a query to delete the record where the id matches $_GET['id']. Example code using mysqli prepared query

// connect to mysql using MySQLi
$mysqli = new mysqli($host, $username, $password, $db_name);

// check to make sure the id exists and it is a number
if(isset($_GET['id']) && ctype_digit($_GET['id']))
{
    // prepare the delete query
    $stmt = $mysqli->prepare("DELETE FROM $tbl_name WHERE id= ?");

    // Issue an error if the prepared statement failed

    if(!$stmt)

    {

trigger_error('Unable to prepare query: ' . $mysqli->error);

    }

// bind the record id to the query

$stmt->bind_param('i', intval($_GET['id']));

 

// execute the prepared query

$result = $stmt->execute();

 

// if the query did not execute trigger an error message

if(!$result)

{

trigger_error("Unable to delete record #{$_GET['id']} from $tbl_name - " . $mysqli->error);

}

 

// check to make sure the query did affect the table.

if($result && $mysqli->affected_rows)

{

echo "Record deleted successfully";

}

}

 

Now because we added the id field to the table we need to go back and alter the code slightly in add-contact.php. Find the prepared query in add-contact.php and change it to this

// specify the columns we are inserting the data into
$stmt = $mysqli->prepare("INSERT INTO $tbl_name (name, email, phone, comment) VALUES (?, ?, ?, ?)");

Now your challenge is to alter the code in view-contact.php over to mysqli. As I said earlier the use of the mysql_ functions are deprecated meaning they are no longer supported and could be removed from future versions of PHP.

Notice: Undefined variable: row in C:\xampp\htdocs\hometown\hometown\view-contactme.php on line 14

I now get this error do I need to add on line 14 id into arrary?

<?php
$host= "localhost";
$user = "root";
$passwd = "";
$database = "test";
$tbl_name = "contactme";

mysql_connect ($host, $user, $passwd) or  die("cannot connect server ");
mysql_select_db( $database) or die("cannot select DB");

$result=mysql_query("SELECT * FROM $tbl_name");

while($rows = mysql_fetch_array($result)){
echo $rows['name'] .  " "  .$rows['email'] .  " " .  $rows['phoneno']  .  " " .  $rows['comments'] . "| <a href=\"delete-contact.php?id={$row['id']}\">Delete</a><br>"; // added delete link. The record id is being passed in the url
}
mysql_close();     //close database
?>

[Barand]

Undefined variable: row

 

 

while($rows = mysql_fetch_array($result)){

 

.... delete-contact.php?id={$row['id']} ...

[G3NERALCHRIS]

while($rows = mysql_fetch_array($result)){

 

.... delete-contact.php?id={$row['id']} ...

Thank you something so simple sometimes its hard to see these things.