Solved

How to Modify PHP Code to detect exact mySQL error

Posted on 2011-03-07
11
284 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
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: 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
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…
The viewer will learn how to count occurrences of each item in an array.

930 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

16 Experts available now in Live!

Get 1:1 Help Now