current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite 
<?php
mysql_connect("localhost","root","");
mysql_select_db("hftwmvirtualdb");
$Booknum = mysql_real_escape_string($_POST['Booknum']); 
$Chapternum = mysql_real_escape_string($_POST['Chapternum']); 
$Versenum = mysql_real_escape_string($_POST['Versenum']); 
$sql = mysql_query("SELECT `VERSETEXT` FROM `booktable` WHERE `BOOKID` = $Booknum AND `CHAPTERID` = $Chapternum AND `VERSENO` = $Versenum");
echo mysql_error();
while($row=mysql_fetch_assoc($sql));
print(json_encode($row));
mysql_close();
?>

I am trying to use posted data from an android application to trigger a query and retrieve the results from the mysql database. The Table has 4 columns, and I'm trying to retrieve the value in the third column by defining the values in the first 3 columns. Each time i clicked the button, I get the parsing error to find out my PHP script was not processing the SQL query. When running the scriptthrough the browser I get the messages:

  • Undefined index: Booknum in C:\wamp\www\GetVerse.php on line 4
  • Undefined index: Chapternum in C:\wamp\www\GetVerse.php on line 5
  • Notice: Undefined index: Versenum in C:\wamp\www\GetVerse.php on line 6
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND CHAPTERID = AND VERSENO =' at line 1
  • Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\GetVerse.php on line 9.

I understand i get the warning messages 1-3 is because I did not submit the post data but the latter I don't know how to fix as I have tried using the correct syntax, I tried removing "=" for "like" and that failed also. What is the problem?.

share|improve this question
2  
The mysql_* functions are no longer maintained and community has begun the deprecation process . Instead you should learn about prepared statements and use either PDO or MySQLi. If you cannot decide, this article will help to choose. If you want to learn, here is a good PDO-related tutorial. –  vascowhite Aug 13 '12 at 14:32
    
if these are numbers you can intval($variable) to it will default to 0. Other than that put the value in quotes BOOKID='$Booknum' –  Waygood Aug 13 '12 at 14:34
    
Unless you require only the last row returned, you should use while($row[]=mysql_fetch_assoc($sql)); –  Karan Punamiya Aug 13 '12 at 14:38
add comment

4 Answers

up vote 2 down vote accepted

The undefined index errors are, as you specified, occurring because you did not submit the post data. This, in turn, is causing the variables $Booknum, $Chapternum, and $Versenum to be empty.

With the empty variables, the MySQL query is being generated with a WHERE clause like:

WHERE `BOOKID` =  AND `CHAPTERID` =  AND ...

The missing values are causing invalid MySQL, hence your error. Additionally, as you've specified (in a comment) that the POST-values are strings (and not integers which is what I would have assumed based on their usage and names), you have to wrap the values in quotes in your MySQL query too. If you do not wrap the values in quotes, even valid strings may cause the query to fail.

To fix this, try something like:

$Booknum = isset($_POST['Booknum']) ? mysql_real_escape_string(trim($_POST['Booknum'])) : null;
$Chapternum = isset($_POST['Chapternum']) ? mysql_real_escape_string(trim($_POST['Chapternum'])) : null;
$Versenum = isset($_POST['Versenum']) ? mysql_real_escape_string(trim($_POST['Versenum'])) : null;
if (!empty($Booknum) && !empty($Chapternum) && !empty($Versenum)) {
    $sql = mysql_query("SELECT `VERSETEXT` FROM `booktable` WHERE `BOOKID` = '" . $Booknum . "' AND `CHAPTERID` = '" . $Chapternum . "' AND `VERSENO` = '" . $Versenum . "'");
    echo mysql_error();
    while($row=mysql_fetch_assoc($sql));
    print(json_encode($row));
    mysql_close();
}

This will verify that the values are properly set - if not, they will be set to null. If all three values are not empty, via PHP's empty(), your query will be executed.

share|improve this answer
    
The post data are strings. If i were to replace line 4 with: "$Booknum = isset($_POST['Booknum']) : false; " it will return me a message of unexpected ':' –  user1310362 Aug 13 '12 at 14:44
    
@user1310362 I've updated my answer to use strings instead of integers. The error you received was because I implemented a ternary expression; the syntax is "(conditional) ? (if true) : (if false)" - your version is missing the ? (if true) part. –  newfurniturey Aug 13 '12 at 15:15
add comment
up vote 1 down vote

The "undefined index" messages you're getting are because those variables are not set. Check that you're actually posting those to the script.

The empty variables are why your query is wrong and you get an error.

Consider using PDO as the "mysql_" commands are deprecated. You should check your inputs before passing them to the query. isset() will work for that.

share|improve this answer
add comment
up vote 0 down vote

This is what your SQL query will look like when the variables are substituted in:

SELECT `VERSETEXT` FROM `booktable` WHERE `BOOKID` =  AND `CHAPTERID` =  AND `VERSENO` =

When the variables contain no content (as they won't if you submit no data), the query is meaningless: the syntax is malformed.

Check whether the data is posted before doing the query. Moreover, it will also profit you to start using parameterised queries (using MySQLi or PDO) for security and convenience.

share|improve this answer
add comment
up vote -2 down vote

CHeck whether the Post data is coming or not, undefined index it is because, there is no data for the variables you have used. SO first verify it and then execte the SQL query.

if(isset($_POST['Booknum']) && isset($_POST['Chapternum']) && isset($_POST['Versenum']))
 {
   $Booknum = mysql_real_escape_string($_POST['Booknum']); 
   $Chapternum = mysql_real_escape_string($_POST['Chapternum']); 
   $Versenum = mysql_real_escape_string($_POST['Versenum']); 
   $sql = mysql_query("SELECT `VERSETEXT` FROM `booktable` WHERE `BOOKID` = $Booknum AND    `CHAPTERID` = $Chapternum AND `VERSENO` = $Versenum");
   echo mysql_error();
   while($row=mysql_fetch_assoc($sql));
   print(json_encode($row));
  }
else
{
 echo "No post data";
 }
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.