Solved

How to Modify PHP Code to detect exact mySQL error

Posted on 2011-03-07
11
283 Views
Last Modified: 2012-05-11
Hi. I am brand new to PHP programming, and I could use some help.

I am using the attached code, and I am getting the error "Item could not be inserted".

How can I modify this code, to see the exact reason why the items can't be inserted into the mySQL database?

Thanks.
function add_data($new_item) {

echo "Attempting to add ".htmlspecialchars($new_item)."<br />";
  $valid_user = $_SESSION['valid_user'];

  $conn = db_connect();

if (!$conn->query("insert into items
     ('".$valid_user."', '".$new_item."')")) {
       throw new Exception ('Item could not be inserted.');
  }

  return true;
}

Open in new window

0
Comment
Question by:esmyhs
11 Comments
 
LVL 6

Expert Comment

by:GregArnott
ID: 35064493
Add:

echo "Error message = ".mysql_error();

Open in new window

0
 

Author Comment

by:esmyhs
ID: 35064614
I'm not exactly sure where to put that. Now I get: "Error message = Item could not be inserted."
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 35064723
Add I right after throw new exception.
0
 

Author Comment

by:esmyhs
ID: 35064784
I added the following:

throw new Exception ("Error message = ".mysql_error());

I get "Error message = "
0
 
LVL 2

Expert Comment

by:Adjroth
ID: 35064813
Replace "throw new Exception ('Item could not be inserted.');" on line 10 with "throw new Exception (mysql_error());". This should display the exact error when the exception is thrown.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:esmyhs
ID: 35064868
All is says now is: "Attempting to add item123" (Which is the item I am trying to insert into the table.)
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35065036
Here is a teaching example script that shows some of the basics of MySQL and the way it interacts with PHP.  Please read it over - code, comments, and man pages references - and post back with any specific questions.  A really good book with great explanations and examples is available from SitePoint:
http://www.sitepoint.com/books/phpmysql4/

HTH, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
    // MAN PAGE: http://php.net/manual/en/function.var-dump.php
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
// MAN PAGE: http://php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($db_connection);

Open in new window

0
 
LVL 5

Accepted Solution

by:
palanee83 earned 500 total points
ID: 35065635
The possible reason for failures are

1. See if item table has more than 2 column, If yes, then mention the column name as part for query.

2. Check the item table schema and see if any  of the column is  unique (item / user).  If yes, make sure your not trying to insert the duplicate value.

3. Make sure your not trying to pass empty / null value if the column in primary / unique.

4. If nothing works out, Please print the query and execute that query directly on MySQL server, This will tell you the exact error message.  



 
0
 

Author Closing Comment

by:esmyhs
ID: 35065765
While I still can't see any sql error messages in my code, this answer helped me find the problem. I was able to run my query directly in mySql and find the problem.

I had more than 2 columns in my table, and I wasn't specifying the column names.

Thanks!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35065785
Hmm... From the accepted solution it looks like you do not understand how to use the PHP programming language to detect MySQL errors.  Hope you but that book.  As Alexander Pope wrote, "A little learning is a dangerous thing."
http://www.sitepoint.com/books/phpmysql4/
0
 

Author Comment

by:esmyhs
ID: 35065833
Ray_Paseur: I appreciate the effort, but it's not possible for me to learn everything from one script. As I've said in the beginning, I am an extremely new programmer to php. I've been a VB programmer for a long time.

I needed a quick solution for this problem, and I got that from palanee83. I will look into the book that you recommended. I know that I have a lot to learn. I just finished the "PHP and MySQL Web Development" book by Luke Welling and Laura Thomson.

Thanks for your effort.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now