Queries

[shadowayex]

Ok, I read through the PHP and MySQL tutorials, and I'm confused. Ok, I'm using a free hosting site called FreeHostia. I've created a MySQL Database and I want to create a page that connects to it and searches it. The main idea is the table in the database has three columns. I want to have three drop down menus that correspond to the rows. I want to make it so people can choose something from at least one of the menus and the page chooses rows from the database that have the selection in the right row and displays them in a table. Lets say there is a table with three rows three rows that look vaguely like this.1 5 41 7 43 4 4If someone selects 1 in the first drop down menu, nothing in the second, and 4 in the third, the page should display a table that has the first two rows because they match what was chosen. Is that clear? I can make an HTML page and take screenshots of what I want to happen step by step if you'd like. Can anyone help me figure this out, because the tutorials lost me after syntax .

[justsomeguy]

It will be easiest to have a series of if statements determine what they chose and run the appropriate query. You would need 7 combinations:1231, 21, 32, 31, 2, 3So one query for if they only chose column one, another if they chose columns 1 and 2, etc. The queries would all just be simple select statements.

$sql = "SELECT * FROM table WHERE column1 = $value1";$sql = "SELECT * FROM table WHERE column1 = $value1 AND column2 = $value2";

[shadowayex]

It will be easiest to have a series of if statements determine what they chose and run the appropriate query. You would need 7 combinations:1231, 21, 32, 31, 2, 3So one query for if they only chose column one, another if they chose columns 1 and 2, etc. The queries would all just be simple select statements.

$sql = "SELECT * FROM table WHERE column1 = $value1";$sql = "SELECT * FROM table WHERE column1 = $value1 AND column2 = $value2";

Ok I think I get what you're saying, but I'm still confused on how to make it so the queries statements get the values from the drop down menus. If you can tell me that then I think I can pull the rest on my own. And this is probably a dumb question but since I'm putting PHP in the document do I have to save it as .php? And would that mess up my validating or all the HTML that's in it? I've never used PHP before.Edit:And I think I might need to make it clear that all three rows have to have something in it because it's like the first two combined make the third one. The point of it is if people wanted to see what a certain combination made (column 1 + comlumn 2 = what) or if they have one thing and want to make something in particular (column 1/column 2 [depending on which they put in] + what = column 3). Is that understandable? and If there is no match I want a box to pop up that tells them there isn't. I know how to make that. Just need some help getting it to execute when nothing is found.

[justsomeguy]

You can get the values from the dropdown menus using the $_POST array. If you have a select element named like this:<select name="dropdown1">And your form submits through post, then PHP would read the chosen value in the $_POST array:$_POST['dropdown1']Also, you can use the mysql_num_rows function on the database result to see how many rows were returned, if there were 0 returned then you can say there were no matches.

[shadowayex]

So the $_POST['dropdown1'] would go in like this:

$sql = "SELECT * FROM table WHERE column1 = $_POST['dropdown1']";

Or did I totally miss that and made myself look like a fool?

[justsomeguy]

You've got the right idea, but you will want to clean anything from $_GET, $_POST, or $_COOKIE before you use it in a SQL query. If it is text data, you need to use mysql_real_escape_string. If it is number data, you need to use either intval (for integers) or floatval (for floats).

$name = mysql_real_escape_string($_POST['name']);$number1 = intval($_POST['number1']);$number2 = floatval($_POST['number2']);

Then you would use the $name, $number1, or $number2 variables instead of the $_POST variable.

[shadowayex]

Oh wow, I just got confused, I understand the concept of it, but like...Ok, I'll type this out to understand it. I can make $name, $number1, and $number2 to whatever I want, but those look good so I'd leave that. I'd stick that wherever it goes and then the query instand would look like this

$sql = "SELECT * FROM table WHERE column1 = $name['dropdown1']";

??? Right? Or number1 or number2. Or did I miss that? Thanks for taking the time to talk to me about this. I'm grasping pieces a little better talking to you than reading the tutorials.

[justsomeguy]

If the column was a number (specifically, an integer):

$number1 = intval($_POST['number1']);$sql = "SELECT * FROM table WHERE column1 = $number1";

If the column is text or character data:

$string1 = mysql_real_escape_string($_POST['string1']);$sql = "SELECT * FROM table WHERE column1 = '$string1'";

Note that string values also need quotes around them in the query, numbers do not.The point of doing this is to sanitize the input to make sure it doesn't contain any malicious code.

[shadowayex]

All the data in the table and in the drop down menus contain only letters and one contains parentheses, there are no numerical values. So like, That new bit of data kinda confused me. I think I understand, but going back to the drop down menus. How do you get the value from the selected into the variable? Sorry if I sound redunant. I know you use the $_POST, but like...I don't get what the connection between the first thing you said to that. Is there another code I need to put that takes the selection to that? The data I've got is for a game called Digimon World 2 and it's DNA Digivolving. Lets says there's three sets of data for a mix for a character called Agumon (I'm pulling this out of thin air so some of it might not even be accurate).

Greymon	 Greymon   AgumonGreymon	 Numemon   AgumonBirdramon   Numemon   Agumon

Someone puts Greymon in the first menu, leaves the second one alone, and then puts Agumon in the third one. I need the query to pull out the first two rows and display them. Step by step, from what I think I picked up, Greymon and Agumon need to go into the $string1 variable and a second variable I'll probably call $string3. So those variable will be put in the query like this:

$sql = "SELECT * FROM digimon WHERE column1 = $string1 AND column3 = $string3";

Right? And then I'll need to put in the code to make it display the matching rows in a table and it come out looking like this:

Greymon   Greymon   AgumonGreymon   Numemon   Agumon

Tables of course having borders but I don't know how to do that in the forums lol.

[justsomeguy]

You may want to read the PHP forms tutorial on the w3schools site, or the PHP tips topic in the PHP forum. Both of them describe how to process a form. If the form is submitted through post, you can access the values in the $_POST array. If it was submitted through get you can access the values through the $_GET array.

[shadowayex]

Ok, I spent all night reading and writing and I switched my original page called DNA-Checker.html to DNA-Checker.php, and I put in what I think is the right PHP for the query and all of that. In the end this is what my page's code looks like.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html><head><title>Digmon World 2 DNA Digivolution</title><link rel="stylesheet" type="text/css"href="mystyle.css" /></head><body class="blueback myftsize"> <table border="0" width="100%" class="black"><!--Top--> <tr><!--Headline-->  <td class="center width100" colspan="5">    <img src="http://dw2.freehostia.com/Images/banner.jpg" class="width80 border" />  </td><!--End of Headline--> </tr><!--End of Top-->   <tr></tr><!--Page-->  <tr><!--Navigation Bar--><td rowspan="2" width="19%" valign="top"><table width="100%"><tr><td class="border orangeback" valign="top" height="370"><a href="http://dw2.freehostia.com/index.html">Home</a><br /><a href="http://dw2.freehostia.com/Digimon/digimon.html">Digimon</a><br /><a href="http://dw2.freehostia.com/Domains/domains.html">Domains</a><br /><a href="http://dw2.freehostia.com/walkthrough.html">Walkthrough</a><br />DNA Digivolution<br /><a href="http://dw2.freehostia.com/Items/items.html">Items</a><br /><a href="http://dw2.freehostia.com/Coliseum/coliseum.html">Coliseum</a><br /><a href="http://dw2.freehostia.com/digi-center.html">Digimon Center</a><br /><br /><br /><img src="http://dw2.freehostia.com/Images/veemon.jpg" /></td></tr></table></td><!--End of Navigation Bar--><td width="2%"> </td><!--DNA Machine--><td class="border orangeback" width="58%" valign="top"> <table border="0" width="100%"> <!--Input Digimon-->  <tr>   <td class="center">First Digimon</td>   <td></td>   <td class="center">Second Digimon</td>   <td></td>   <td class="center">Outcome</td>  </tr>  <tr>   <td class="center">   <form action="DNA-Checker.php" method="post">    <select name="digimon1">     <option>[Find Digimon]</option>     <option value="Greymon">Greymon</option>    </select>   </td>   <td width="1%">+</td>   <td class="center">    <select name="digimon2">     <option>[Find Digimon]</option>     <option value="Greymon">Greymon</option>    </select>   </td>   <td width="1%">=</td>   <td class="center">    <select name="outcome">     <option>[Find Digimon]</option>     <option value="Agumon">Agumon</option>    </select>   </td>  </tr>  <tr>   <td class="center" colspan="5">     <input type="submit" value="Submit">    </form>   </td>  </tr> <!--End of Input Digimon--> <!--Output-->  <tr>   <td class="center" colspan="5" rowspan="100"><?php// Connecting, selecting database$link = mysql_connect('mysql4.freehostia.com', 'cyldaw_dna', 'blackwargreymon')    or die('Could not connect: ' . mysql_error());echo 'Connected successfully';mysql_select_db('cyldaw_dna') or die('Could not select database');// Performing SQL query$digimon1 = mysql_real_escape_string($_POST['digimon1']);$digimon2 = mysql_real_escape_string($_POST['digimon2']);$outcome = mysql_real_escape_string($_POST['outcome']);$query = "SELECT * FROM digimon WHERE FirstDigimon = '$digimon1' AND SecondDigimon = '$digimon2' AND Outcome = '$outcome'";$result = mysql_query($query) or die('Query failed: ' . mysql_error());// Printing results in HTMLecho "<table border='1'><tr><th>Digimon 1</th><th>Digimon 2</th><th>Outcome</th></tr>";while($row = mysql_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['FisrtDigimon'] . "</td>";  echo "<td>" . $row['SecondDigimon'] . "</td>";  echo "<td>" . $row['Outcome'] . "</td>";  echo "</tr>";  }echo "</table>";// Closing connectionmysql_close($link);?>   </td>  </tr> <!--End of Output--> </table></td><!--End of DNA Machine--><td width="2%"> </td><!--Ad Section--><td rowspan="2" width="19%" valign="top"><table width="100%"><tr><td class="border orangeback" valign="top" height="370">Ads</td></tr></table></td><!--End of Ad Section-->  </tr>  <tr><td><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /></td></tr><!--End of Page--> </table></html>

And here's a link to the page: http://dw2.freehostia.com/DNA-Checker.phpProblem is, the page says it's connected and everything, but when I try to query, nothing happens. Did I screw something up?Note: I'm in the process of experiementing with DIVs and soon the page format will be switched to include them.

[justsomeguy]

What do you mean nothing happens? There is a lot of code after the query outputting HTML, does it do that? Do you get any error messages? That query will only return matches where all three columns match, so make sure you make the correct choices for all three columns. There's also a typo here:echo "<td>" . $row['FisrtDigimon'] . "</td>";Also, change this line:while($row = mysql_fetch_array($result))to this:while($row = mysql_fetch_assoc($result))mysql_fetch_array returns an array with numbered indexes, mysql_fetch_assoc returns an array with the column names as indexes.

[shadowayex]

Ok I know what the problem is, but I don't know how to fix it. You said it will only show results when all match. I need to make it so the [Find Digimon] means nothing to the query so when I have something like 1.Greymon 2.[Find Digimon] and 3.Agumon, the page does a query like

$query = "SELECT * FROM digimon WHERE FirstDigimon = '$digimon1' AND Outcome = '$outcome'";instead of$query = "SELECT * FROM digimon WHERE FirstDigimon = '$digimon1' AND SecondDigimon = '$digimon2' AND Outcome = '$outcome'";

Can I do that?

[justsomeguy]

You have this code block:

while($row = mysql_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['FisrtDigimon'] . "</td>";  echo "<td>" . $row['SecondDigimon'] . "</td>";  echo "<td>" . $row['Outcome'] . "</td>";  echo "</tr>";  }

Is it printing out those tr tags? You would need to view the source code to check, just looking at the web page wouldn't tell you that. If it's printing the rows but it is not printing the data in the rows, then the row doesn't contain elements called "Outcome", "SecondDigimon", etc. You can do a print_r on $row to see what the fields are. If it is not printing the tr tags at all, then no matches are being returned from the database.

[shadowayex]

You have this code block:

while($row = mysql_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['FisrtDigimon'] . "</td>";  echo "<td>" . $row['SecondDigimon'] . "</td>";  echo "<td>" . $row['Outcome'] . "</td>";  echo "</tr>";  }

Is it printing out those tr tags? You would need to view the source code to check, just looking at the web page wouldn't tell you that. If it's printing the rows but it is not printing the data in the rows, then the row doesn't contain elements called "Outcome", "SecondDigimon", etc. You can do a print_r on $row to see what the fields are. If it is not printing the tr tags at all, then no matches are being returned from the database.

Check my modified post, sorry.

[justsomeguy]

Right, that's where you need the if structure I was talking about. First, you need a value for the default options, like a blank value.<option value="">[Find Digimon]</option>And then you need to check which ones were chosen.

$empty1 = $_POST['digimon1'] == "";$empty2 = $_POST['digimon2'] == "";$empty3 = $_POST['outcome'] == "";if (!$empty1 && $empty2 && $empty3){  //they chose the first one only}elseif ($empty1 && !$empty2 && $empty3){  //they chose the second one only}elseif (!$empty1 && !$empty2 && $empty3){  //they chose the first and second only}

etc, up above I described the 7 cases you need. The if statements would contain the query being built, and after the if structure you would call mysql_query with whichever query was built.

[shadowayex]

So I finish that and put it right before the query? Or where would it be placed? I think you might've answered it but as you can tell I'm not that swift .

[justsomeguy]

Actually there's another, possibly easier way of doing it (although the above will give you more control). You could just have the devault values like this:<option value="%">[Find Digimon]</option>And your query would need to use LIKE instead of the equals.$query = "SELECT * FROM digimon WHERE FirstDigimon LIKE '$digimon1' AND SecondDigimon LIKE '$digimon2' AND Outcome LIKE '$outcome'";The query would be slower, and you wouldn't be able to tell what they chose and what they didn't vs. the if structure above, but it's a quick fix.

[shadowayex]

I think I'd like the If one better, how do I pull it off?EDIT: I think I figured it out, would I have to put the queries for each case in the if statement?

[shadowayex]

YAY!!! I GOT IT!!!! Lol thanks for all your help dude. You do this stuff for a living don't you? Anyways, thanks for everything. I'm really psyched now. I'll proly be back with more questions eventually lol.