[PHP] Can not read from mySQL 5

[nasty_nich]

Hi. I am having a problem with this php page that i have made that is supposed to connect to my database, read from it based on values gotten form an html form, and echo the data in a nice format.
<?php
$con = mysql_connect("localhost","vocabpr","******");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hubris86_latinvocab", $con);

if($_POST[PartofSpeech] == "verbs")
{
$result = mysql_query("SELECT * FROM $_POST[PartOfSpeech] WHERE Unit='$_POST[unit]' and WHERE Lesson='$_POST[lesson]' LIMIT 0, 9999");

$problemNumber = 1;
while($row = mysql_fetch_array($result))
{
echo $problemNumber . ". " . $row['FirstPrinciplePart'] . ", " . $row['SecondPrinciplePart'] . ", " . $row['ThirdPrinciplePart'] . ", " . $row['FourthPrinciplePart'];
echo "<br />";
$problemNumber++;
}
mysql_close($con);
}
?>
the above code wont spit out anything at all not even an error. I am able to connect to my mysql database and write to it with the same connection info but not write to it. Please help me resolve this issue. Thanks in advance. And if you are wondering what this is for it is a latin vocab storage and retrieval program to generate tests and such.

[PeejAvery]

Well, you are calling invalid indexes of the $_POST variable in your query and an extra WHERE. So your query will not return anything.

You will also notice the lack of mysql_real_escape_string(). Without that, you are susceptible to SQL injection (http://en.wikipedia.org/wiki/SQL_injection).

Try...

$query = "SELECT * FROM '" . mysql_real_escape_string($_POST['PartOfSpeech']) . " ";
$query .= "WHERE Unit = '" . mysql_real_escape_string($_POST['unit']) . "' ";
$query .= "AND Lesson = '" . mysql_real_escape_string($_POST['lesson']) . "' LIMIT 0, 9999";

$result = mysql_query($query);

EDIT: Fixed missing single-quotes.

[nasty_nich]

Thank you for the quick reply PeejAvery. I really appreciate the information on sql injection, but i still do not get any results when this page gets called from the form page. I am sure that the table does have entries with the parameters i am using in the html form. Could it be that the single quotes are messing something up or maybe the absence of single quotes are messing something up in this:

$_POST['PartOfSpeech']
$_POST[Unit]

I have double checked the connection info and it is okay. Could some one please tell me which is correct? The one with single quotes or the one without. I have looked around and have seen many examples that use both of them seemingly interchangably. Once again, thank you for the quick reply.

[PeejAvery]

Sorry, that is what I was trying to do. I fixed the first $_POST missing the quotes, but missed the second two. I edited my previous post with the corrections.

You see, the quotes are needed because $_POST returns an associative array. Therefore the indexes are strings. You don't need the quotes if those are declared constant (which I doubt in this case).

[nasty_nich]

Thank you again PeejAvery for such a quick reply. I put in the single quotes like you said to do but the page is still not being generated with any data from my database in it. I tried parsing the page with single quotes, no quotes, and double quotes in the $row[FirstPrinciplePart] (for all of the $row calls). I do know that the page is being parsed by the server though because when i was adding the single quotes to the $POST calls I had a typo and it generated an error. Any ideas on what is going on?

[PeejAvery]

Echo the query. Then copy and paste that into phpMyAdmin to see if it really is generating any results from the database.

Also, are you sure that you aren't getting any errors in your error.log file?

[nasty_nich]

hmm when i put it in the sql query into phpmyadmin i get this error:
Error

SQL query: Documentation

SELECT *
FROM 'verbs'
WHERE Unit = '1'
AND Lesson = '1'
LIMIT 0 , 9999

MySQL said: Documentation
#1064 - 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 ''verbs' WHERE Unit = '1' AND Lesson = '1' LIMIT 0,9999' at line 1

I do not see what is wrong with my query, any ideas?

[nasty_nich]

Wait, i just tried it using this query and got results: SELECT * FROM `verbs` WHERE Unit ='1' and Lesson = '1' are those `s needed, i thought they were supposed to be 's?

[PeejAvery]

Around table names and columns you can use an optional angled apostrophe. For values you use a single quote.

[nasty_nich]

I was thinking about my problem since it still isn't resolved and could it be that when someone types in numbers for the unit and lesson parameters in the html form those numbers are being taken as a string by php instead of an integer? In my mysql table Unit and Lesson are both integers. Here is the html form:

<html>
<body>
<p align=center>
<br><br>

<form action="writetest1-0a.php" method="post">
Part of Speech: <select name="PartOfSpeech">
<option>verbs</option>
<option>nouns</option>
<option>adjectives</option>
</select><br>
Unit: <input type="text" name="unit" /><br>
Lesson: <input type="text" name="lesson" /><br>
<input type="submit" value="Generate Test"/><br>
</form>


</p>
</body>
</html>


and here is the entire writetest1-0a.php file:


<html>
<body>
<p align=center>
<?php
$con = mysql_connect("localhost","hubris86_vocabpr","*******");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hubris86_latinvocab", $con);

if($_POST['PartofSpeech'] == "verbs")
{
$query = "SELECT * FROM `" . mysql_real_escape_string($_POST['PartOfSpeech']) . "` ";
$query .= "WHERE Unit = '" . mysql_real_escape_string($_POST['unit']) . "' ";
$query .= "AND Lesson = '" . mysql_real_escape_string($_POST['lesson']) . "' LIMIT 0, 9999";
$result = mysql_query($query);

$problemNumber = 1;
while($row = mysql_fetch_array($result))
{
echo $problemNumber . ". " . $row['FirstPrinciplePart'] . ", " . $row['SecondPrinciplePart'] . ", " . $row['ThirdPrinciplePart'] . ", " . $row['FourthPrinciplePart'];
echo "<br />";
$problemNumber++;
}
mysql_close($con);
}


if($_POST['PartOfSpeech'] == "nouns")
{
$query = "SELECT * FROM `" . mysql_real_escape_string($_POST['PartOfSpeech']) . "` ";
$query .= "WHERE Unit = '" . mysql_real_escape_string($_POST['unit']) . "' ";
$query .= "AND Lesson = '" . mysql_real_escape_string($_POST['lesson']) . "' LIMIT 0, 9999";
$result = mysql_query($query);

$problemNumber = 1;
while($row = mysql_fetch_array($result))
{
echo $problemNumber . ". " . $row['NominativeSingular'] . ", " . $row['GenitiveSingular'] . " " . $row['Gender'] . ".";
echo "<br />";
$problemNumber++;
}
mysql_close($con);
}



if($_POST['PartOfSpeech'] == "adjectives")
{
$query = "SELECT * FROM `" . mysql_real_escape_string($_POST['PartOfSpeech']) . "` ";
$query .= "WHERE Unit = '" . mysql_real_escape_string($_POST['unit']) . "' ";
$query .= "AND Lesson = '" . mysql_real_escape_string($_POST['lesson']) . "' LIMIT 0, 9999";
$result = mysql_query($query);

$problemNumber = 1;
while($row = mysql_fetch_array($result))
{
echo $problemNumber . ". " . $row['MasculineForm'] . ", " . $row['FeminineForm'] . " " . $row['NeuterForm'] . ".";
echo "<br />";
$problemNumber++;
}
mysql_close($con);
}

?>
</p>
</body>
</html>


and once again thank you PeejAvery for continuing to work with me to solve this problem.

[PeejAvery]

PHP is a loosely typed language. It will automatically convert between data types. So no, it's not an issue with strings versus integers. I'll take a closer look tomorrow morning.

[nasty_nich]

okay, thank you peejavery.

[nasty_nich]

I just made a testquery.php page that just has a query with nothing special like the ability to select different tables ie this:

<?php
$con = mysql_connect("localhost","hubris86_vocabpr","******");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("hubris86_latinvocab", $con);

$result = mysql_query("SELECT * FROM verbs");

while($row = mysql_fetch_array($result))
{
echo $row['SecondPrinciplePart'] . " " . $row['English'];
echo "<br />";
}

mysql_close($con);
?>

and it worked like a charm so it has to be something wrong with the syntax of my writetest1-0a.php page (or maybe the method=post isnt working correctly in the form?). I wish i could figure it out, been playing with it for over a week now and still no luck.

[PeejAvery]

I have looked it over multiple times and do not see any syntax errors. I mentioned that you need to check the error.log file for those. Did you yet?

Personally, I believe it has something to do with your queries or connection. Why don't you use phpMyAdmin to output your hubris86_latinvocab database to a sql file? Then upload it here so we can play with it also.

[nasty_nich]

i have checked the error.log file and there is nothing except for when i had a typo before, but i have fixed it and it has no new entries. I have attached the .sql file export for my database and tables (codeguru won't let me upload .sql files so i had to zip it).

[PeejAvery]

You're going to hate yourself...In the HTML page you have PartOfSpeech and in the PHP page you have PartofSpeech.

I didn't notice it until it threw me an error in the error log. Yours too should have done the same unless you have altered the php.ini settings for errors.

[nasty_nich]

Woot that worked!!! Thanks Peejavery for working me through this issue. And for some reason i did not get an error in my error log when i had the typo, but at least it is working now. Thanks man. One more quick question. If i wanted to allow people to use a wildcard (*) into the unit and lesson fields in the html form to select all units or lessons or all of both how would i do this since in the database the unit and lesson fields are ints not strings?

[PeejAvery]

String replace * to %. % is the MySQL wildcard.

[nasty_nich]

do i need to do something special when using % to search through an int column because i tried that by putting it in the $_POST['unit'] and for lesson and it did not work.

[nasty_nich]

hey Peejavery i could not get what you said working, but i came up with another solution:

$query = "SELECT * FROM `" . $partofspeech . "`";
if(($_POST['unit'] != "*") || ($_POST['lesson'] != "*"))
{
$query .= " WHERE ";
}
if($_POST['unit'] != "*")
{
$query .= "Unit = '" . $unit . "' ";
}
if(($_POST['unit'] != "*") && ($_POST['lesson'] != "*"))
{
$query .= "AND ";
}
if($_POST['lesson'] != "*")
{
$query .= "Lesson = '" . $lesson . "'";
}
$query .= " LIMIT 0, 9999";

can you see any security issues with this method of using a pseudo wildcard?

[PeejAvery]

Don't waste all that. Simply use a string replace.

$query = str_replace('*', '%', $query);