Need help making 2 queries on the same table

Beginners - Learning PHP
#1

I am trying to pull an image path from a database and display the corresponding image on a page, based on which item the user selects from a dropdown. I am absolutely a novice, but I was able to modify http://www.w3schools.com/php/php_ajax_database.asp?output=print over at w3schools to display an image (path) to a database instead of the names / jobs / etc with the code below.

[php]

<?php $q = intval($_GET['q']); $con = mysqli_connect('','','',''); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } mysqli_select_db($con,"shapes_images"); $sql="SELECT image FROM shapes_images WHERE id = '".$q."'"; $result = mysqli_query($con,$sql); echo ""; while($row = mysqli_fetch_array($result)) { echo ""; echo ""; echo ""; } echo "
"; ?>

[/php]

the code on my page is as W3S has it -

<script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getImage.php?q="+str,true);
xmlhttp.send();
}



</script>
</head>
<body>
<div class="left">
<form>
<select name="users" onChange="showUser(this.value)">
<option value="">Select a color:</option>
<option value="1">Blue</option>
<option value="2">Red</option>
<option value="3">Yellow</option>
<option value="4">Green</option>
</select>
</form>
<br>

</div>

Which works correctly. When you click ‘blue’ on the dropdown, a blue square image appears, reading the path in from the database. The problem is when I try to do more than one dropdown menu. I assumed (apparently incorrectly :slight_smile: ) that if I duplicated the function and changed the name, and duplicated the php script I would have two independently controllable dropdowns on the page, each displaying whichever color block (left is squares, right is triangles) the user had chosen. It currently displays both dropdowns and will display whatever image it points to, but as it stands, when you select an image in the second dropdown, it replaces that in the first, and vice versa as opposed to displaying them side by side. As I said, im a bit of a novice here but venture I am not understanding the PHP function properly

Can anyone point me in the right direction? thank you.

#2

Please post the code you are having trouble with, it’s much better to point out the errors you’ve made than to do the work for you.

#3

Thanks for the reply. I wasnt expecting anyone to do the work for me - apologies if it came off that way. I suppose the specific part im having trouble with is this, specifically what is happening in lines 16 - 28 -

[php]

<?php $q = intval($_GET['q']); $con = mysqli_connect('','','',''); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } mysqli_select_db($con,"shapes_images"); $sql="SELECT image FROM shapes_images WHERE id = '".$q."'"; $result = mysqli_query($con,$sql); echo ""; while($row = mysqli_fetch_array($result)) { echo ""; echo ""; echo ""; } echo "
"; ?>

[/php]

I understand that it is saying something like - hit this database, pull in the image and display it in a row on a table (based on the ID in the dropdown menu). What I dont understand is why, and/or how to say perform this function, display the result, then perform another function without overriding the first result. As I said, I am a novice and my first solution was to duplicate the function and change its name. Hope that helps.

,

#4

No worries, I was really hoping for the code where you tried doing this twice, but let’s give it a shot anyway.

[php]<?php
$q = intval($_GET[‘q’]); // unnecessary, you should be using parameterized queries (mysqli support it)

// connect to a mysql databse
$con = mysqli_connect('','','','');
if (!$con)
{
// something wrong happened with the connection, print an error
die('Could not connect: ' . mysqli_error($con));

}

mysqli_select_db($con,“shapes_images”);

// here we start printing the table
echo "

"; 
// ok, so here we want some data from the database, lets first write the query itself
$sql="SELECT image FROM shapes_images WHERE id = '".$q."'"; // you should never insert variables directly into a query

// then actually perform the query
$result = mysqli_query($con,$sql);

// while is a loop, so as long as we get a new row from the database (using the query above), do what’s inside the curly braces {}
while($row = mysqli_fetch_array($result))
{
// this will be repeated for every row we get from the db
echo “

”;
echo “<img src=’/”.$row[‘image’]."’ />";
echo “”;
}
// loop finished, all images printed, let’s close the table
echo “
”;

// don’t end with ? > there is actually more reasons not to end the php file with it. ex: if you have anything after ? > you can no longer modify header data / cookies, it can be a pain.
[/php]

it seems like you want a new set of data displayed the same way, so we copy that part

[php]<?php
$q = intval($_GET[‘q’]);

$con = mysqli_connect('','','','');
if (!$con)
{
die('Could not connect: ' . mysqli_error($con));

}

mysqli_select_db($con,“shapes_images”);

echo "

"; 
$sql="SELECT image FROM shapes_images WHERE id = '".$q."'"; // you should never insert variables directly into a query

$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result))
{
echo “

”;
echo “<img src=’/”.$row[‘image’]."’ />";
echo “”;
}
echo “
”;

// I am guessing you want another table with data, so we must repeat that again.

echo "

"; 
// here you can do a different query
$sql="SELECT image FROM shapes_images WHERE id = '".$q."'"; // you should never insert variables directly into a query

$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result))
{
echo “

”;
echo “<img src=’/”.$row[‘image’]."’ />";
echo “”;
}
echo “
”;[/php]

A few notes.

Parameterized queries are very important. Before putting code online you should make sure your code is secure (btw: i don’t mean posting here, I mean making websites available online)

You should split logic and view, but this will probably come later. This means that you do all the data-work first, like building result arrays, getting data from databases/APIs, etc. Then afterwards you can print html, preferably not using echo’s or print, but just stopping php execution and writing plain html. It looks better when inserting php into html than inserting html into php ^^

#5

@JimL,

sorry for the delayed response. Thank you so much for taking the time to go over that. After some tinkering, I got it to work. I think my problem was with the database, not the function. This part of what you wrote set me thinking -

// while is a loop, so as long as we get a new row from the database (using the query above), do what's inside the curly braces {}

In my database, the image paths were all in the same column, tied to an ID. so (as I understand it) by duplicating the function, I was just giving it another name, but telling it to do the same action, which is why it appeared to be ‘overriding’ the previous action. I added a column in my database to control the second set of images, duplicated the function and it worked! Im sure its not the most elegant solution, but 2 items show up simultaneously.

thank you again for the help.

Sponsor our Newsletter | Privacy Policy | Terms of Service