Grabbing data from db by clicking on image

[galahad2]

Hi,

I now have a PHP page which contains nicely ordered images all extracted from a mySQL db... I was trying to figure a way where if I click on any of these images (call them thumbnails really) then it loads a new page which looks the same except that a column on the page now displays a bigger version of the image plus the various other (text) fields from the db. In essence, I want to click on any thumbnail and get the details of that product load with a bigger image on a new page (or even the same page if possible).

I played with some code that I'd used previously for a different purpose- this was for making actual changes to the db, whereas this is just grabbing and displaying the data- I was trying to work with the thumbnail as a "select" i.e firing off a bit of code to grab the relevant record from the db- but couldn't get it working:

The original code that displays the thumbnails on their page (three per row) is:

//code
while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
echo '<td><img src="images/products/' . $title . '.jpg"></td>';
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}
//end code

And the changed code I attempted to make in order to make clicking the thumbnail grab the details of the relevant field:

//code
while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
echo '<td><input type="image" src="images/products/' . $title . '.jpg" name="showdetails"></td>';
echo '<td><input name="_Image" size="30" type="hidden" value=$title /></td>';
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}
//end code

I tried using the "hidden" input as a way of storing $title so it could be used in the showdetails.php called by the input, which is as follows:

//code
<?php
if($_POST['showdetails'])
{
$title =@$_POST['_Image'];

include ('inc/dbconnect.php');

$query = "SELECT * FROM trios WHERE Image = '$title'";
}
if($result = mysql_query($query))
{
header('location: showdetails.php');
exit;
}
else
{
echo "ERROR: ".mysql_error();
}
?>
//end code

The idea was that I could then output the record fields with something like the following:

//code
while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
$title2 = $row["Text1"];
$title3 = $row["Text2"];
echo "<form action=\"display.php\" method=\"post\" name=\"display\">
<table width=\"500\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">
<tr>
<td width=\"200\">Image" . $title . "</td>
<td><input name=\"_Image\" size=\"30\" value='$title' type=\"hidden\" /></td>
</tr>
<tr>
<td>Details " . $title2 . "</td>
<td><input name=\"_Text1\" size=\"30\" value='$title2' type=\"hidden\" /></td>
</tr>
<tr>
<td>Price</td>
<td><textarea name=\"_Price\" cols=\"40\" rows=\"5\" value='$title3'>$title3</textarea></td>
</tr>
</table><br />
</form>";
$count++ ;
}
//end code

I'm sure it can be done much more simply (not that this works anyway!)...?

[jatar_k]

hopefully your image has some name that is unique and somehow relevant to the db

if so the logic is as such

use the unique identifier to create a link on your image. something like this if you have an id

<a href="/getbigimganddetail.php?img=12345"><img src="someimg.jpg" width="80" height="80"></a>

or maybe this for a string

<a href="/getbigimganddetail.php?img=my_cool_image"><img src="someimg.jpg" width="80" height="80"></a>

the getbigimganddetail.php would then grab the $_GET parameter, validate it and use it to select the appropriate data from your database.

get that portion working and then worry about display as it is fairly straight forward after that

[d40sithui]

Typically, using text($title) as the identifier is not a practical (or easy) way to go.
If you have an ID field that makes each image and each item unique, it would make things a lot simpler.
So all you would need to do is user $_GET to get the ID after linking it and retrieve all the item's information on this value.
You could end up having something like this

//code
while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
$itemId = $row['itemId'];
echo '<td><a href="display.php?itemId=$itemId"><img src="images/products/' . $title . '.jpg"></a></td>';
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}
//end code

[edit]
blast! jatar beat me to the punch
[/edit]

[galahad2]

Thanks guys, I've set this up as follows:

//code
while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
$Reference = $row['Reference'];
echo '<td><a href="display.php?Reference=$Reference"><img src="images/products/' . $title . '.jpg"></a></td>';
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}
//end code

Then on display.php, to test (not sure if this is ok) this PHP:

<?php
if($_POST['display'])
{
// Get the search variable from URL
$Reference =@$_POST['Reference'];

include ('inc/dbconnect.php');

// Build SQL Query
$query = "SELECT * FROM trios WHERE Reference = '$Reference'"; // specify the table and field names for the SQL query
}
if($result = mysql_query($query))
{
header('location: display.php');
exit;
}
else
{
echo "ERROR: ".mysql_error();
}
?>

But I'm getting this error on the page: ERROR: Access denied for user 'username'@'localhost' (using password: NO)

However the dbconnect.php is definitely ok (includes username and password), and I can access the database with the specified password without any problem.

Any ideas?

[galahad2]

I should add that I contacted my host about this and they said it wasn't a mySQL issue but something to do with the code not remembering the access details in the include file? So I added the include statement into the while loop on the first page, which resulted in an odd "Empty query" message... then tried again, and we're back to Access Denied.

Any ideas?

[d40sithui]

It may be this line returning false and thus you never get to the

include ('inc/dbconnect.php');

if($_POST['display'])

Quick remedy is to move the include db function to the top of the page outside any loops.

[galahad2]

I saw that and yes, removing the if clause did stop the error. Only problem is now when I click the link, it starts to browse to the next page, but never gets there- eventually it times out but it generates no error.

I changed the code slightly as follows:

On the first page

//code
while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
$refnumber = $row['Reference'];
echo '<td><a href="display.php?Reference=$refnumber"><img src="images/products/' . $title . '.jpg"></a></td>';
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}

On the display.php:

//code
<?php
include ('inc/dbconnect.php');

$refnumber =@$_GET['Reference'];

// Build SQL Query
$query = "select * from trios WHERE Reference like $refnumber";
if($result = mysql_query($query))
{
//go to the new member confirmation page
header('location: display.php');
exit;
}
else
{
echo "ERROR: ".mysql_error();
}
?>

[galahad2]

I've now managed to get around that problem (removed the include from inside the while loop), but need a way of displaying the grabbed db fields on the display.php page.

This is what I've got to so far:

<?php
include ('inc/dbconnect.php');

// Get the search variable from URL
$refnumber =@$_GET['Reference'];

// Build SQL Query
$query = "select * from trios WHERE Reference = '$refnumber'"; // specify the table and field names for the SQL query
//Now I try to display the results on the page
if($result = mysql_query($query))
{
$title = $row["Image"];
$title2 = $row["Title"];
$title3 = $row["Reference"];

echo "<table width=\"500\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">
<tr>
<td width=\"200\">Image " . $title . "</td>
</tr>
<tr>
<td>Title " . $title2 . "</td>
</tr>
<tr>
<td>Reference " . $title3 . "</td>
</tr>
</table>";
}
else
{
echo "ERROR: ".mysql_error();
}
?>

But it's not displaying...

[d40sithui]

Oh that should be an easy fix.
You forgot to assign the result to an array.

// Build SQL Query
$query = "select * from trios WHERE Reference = '$refnumber'"; // specify the table and field names for the SQL query
$result = mysql_query($query);

//if valid query (rows found)
if(mysql_num_rows($result)){
{

$row = mysql_fetch_assoc($result);
$title = $row["Image"];
$title2 = $row["Title"];
$title3 = $row["Reference"];

[galahad2]

Hmm, I tried that, but I just get the HTML text in the td's, I don't get the output from $title, $title2 and $title3 for some reason, even though I added in $row = mysql_fetch_assoc($result); to the if.

?

[d40sithui]

Sounds like there is a problem with your query.
Do a manual query in the db and see if you get anything.

<?php
include ('inc/dbconnect.php');

// Get the search variable from URL
$refnumber = $_GET['Reference'];

// Build SQL Query
$query = "select * from trios WHERE Reference = '$refnumber'"; // specify the table and field names for the SQL query
$result = mysql_query($query);

if(mysql_num_rows($result)) {

$row = mysql_fetch_assoc($result);
$title = $row["Image"];
$title2 = $row["Title"];
$title3 = $row["Reference"];

echo "<table width=\"500\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">
<tr>
<td width=\"200\">Image " . $title . "</td>
</tr>
<tr>
<td>Title " . $title2 . "</td>
</tr>
<tr>
<td>Reference " . $title3 . "</td>
</tr>
</table>";
}
else {
echo "ERROR: ".mysql_error();
}
?>

[galahad2]

I changed the query to just "select * from trios" and this, intertestingly, brings up just the first record in the table as a result (no matter which graphic I click on).

Also the display.php displays in the browser with the URL display.php?Reference=$refnumber

Is this correct? Should it not show the particular product ID (Reference in this case)e.g Reference=TRS1

[galahad2]

I also tried this as the query:

"select * from trios WHERE Reference like 'TS9'" (and other product references)

- and it works. But it doesn't like the variable $refnumber for some reason, though I have no idea why. Any ideas? The code on the preceding page, where the variable is generated, hasn't been changed.

[d40sithui]

I changed the query to just "select * from trios" and this, intertestingly, brings up just the first record in the table as a result (no matter which graphic I click on).

This is correct. If you have multiple rows and you do a select *, you will get all the rows. BUT, since you onle assigned the first result to $row, you will only get the data for one. To get all of them, you'd do while($row = mysql_fetch_assoc($result)){}. but for the purpose of this task, we do not need that.

Also the display.php displays in the browser with the URL display.php?Reference=$refnumber
Is this correct? Should it not show the particular product ID (Reference in this case)e.g Reference=TRS1

This is not correct. It should display the value of $refnumber, instead of the variable name. It is the way you code the link where PHP does not parse the variable.
rewrite this::

while ($row= mysql_fetch_array($result)) {
$title = $row["Image"];
$Reference = $row['Reference'];
echo '<td><a href="display.php?Reference=$Reference"><img src="images/products/' . $title . '.jpg"></a></td>';

to

while ($row= mysql_fetch_assoc($result)) {
$title = $row['Image'];
$Reference = $row['Reference'];
echo '<td><a href="display.php?Reference='.$Reference.'"><img src="images/products/' . $title . '.jpg"></a></td>';

[galahad2]

Okay, I've now changed as above, and the display.php URL shows as display.php?Reference=TR5 (etc. depending on the item clicked on)

But the fields themselves from the appropriate record still don't display on the page for some reason. The code I now have for display.php is:

//start code
<?php
include ('inc/dbconnect.php');

// Get the search variable from URL
$refnumber =@$_POST['Reference'];

// Build SQL Query
$query = "select * from trios WHERE Reference like '$refnumber'"; // specify the table and field names for the SQL query
$result = mysql_query($query) or die("The System is undergoing maintenance at the moment and will be available shortly");
if($result = mysql_query($query))
{
$row = mysql_fetch_assoc($result);
$title = $row["Image"];
$title2 = $row["Title"];
$title3 = $row["Reference"];

echo "<table width=\"500\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">
<tr>
<td>Page " . $title . "</td>
</tr>
<tr>
<td>Paragraph Number '$title2' </td>
</tr>
<tr>
<td>Reference '$title3' </td>
</tr>
</table>";
}
else
{
echo "ERROR: ".mysql_error();
}
?>
//end code

All that displays on the page is the text from inside the td's. The query presumably runs because it doesn't generate an error, but it isn't picking anything up from the db. However as before, if I substitute the $refnumber in the query for an actual value such as TR1, it works.

?

[galahad2]

Actually, ignore the above post- I'd changed GET to POST when declaring the $refnumber variable- changed to GET and it's now working.

Thanks for all your assistance. :)