Solved

How to Modify PHP Code to detect exact mySQL error

Posted on 2011-03-07
11
285 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 109

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 109

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add different cell to otherwise similiar row 4 39
AWS EC2 & RDS Instance 5 35
Mysql Left Join Case 10 53
error in my cursor 5 29
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 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