Solved

How to Modify PHP Code to detect exact mySQL error

Posted on 2011-03-07
11
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 39

Expert Comment

by:Aaron Tomosky
ID: 35064723
Add I right after throw new exception.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 110

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 110

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

734 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