Page 1 of 2

[SOLVED] Query Problem!

Posted: Sun Jul 11, 2004 11:35 am
by Joe
I have made up a query where I am trying to select data from two seperate tables. It is confusing to explain but I will start off with the code:

Code: Select all

<?php
$link = mysql_connect($DBHost, $DBUser, $DBPass);
mysql_select_db($DBName) or die("Could not connect!" . mysql_error());
$query = "SELECT * FROM orders AS packages, images AS photo WHERE photo.tag='0'";
$result = mysql_query($query) or die(mysql_error());

while (true)
{
$row = mysql_fetch_array($result);
if ($row == false) break;

echo "<br><td><b>ID:</b> ".$row['ID']."</a></td><br>";	
echo "<td><b>Date:</b> ".$row['date']."</td><br>";

echo "<td><b>Payment Method:</b> ".$row['payment']."</td><br>";
echo "<td><b>Full Name:</b> ".$row['fullname']."</td><br>";
echo "<td><b>Email:</b> <a href='mailto:".$row['email']."'>".$row['email']."</a></td><br>";

$name = $row['name'];
$name = str_replace("-","", $name);

echo $row[$name]."<br>";

echo "<br><hr color='gray'>";
}
I am making a form in the administration panel where orders etc... can be viewed and everything works great but I am unable to select the product name to view the customers choice of value. That is line:

echo $row[$name]."<br>";

Dont get confused by the str_replace(), I require that as the orders table does not have the -'s but the images table does. Again, this is hard to explain.

I was thinkin that perhaps it was my query structure:

$query = "SELECT * FROM orders AS packages, images AS photo WHERE photo.tag='0'";


Any help appreciated!


Joe 8)

Posted: Sun Jul 11, 2004 11:40 am
by feyd
you're trying to find a field named $name, which likely doesn't exist or it could potentially be different for each and every user you are selecting..

Posted: Sun Jul 11, 2004 11:43 am
by Joe
Well, each column which is created with the variable $name holds each individual customers cost and I am sure that it exists. Is there any way around this, I have been trying for 2days now with no luck and im pulling my hair out :(

Regards


Joe 8)

Posted: Sun Jul 11, 2004 11:57 am
by ol4pr0
Did you try that query in a query window instead of using a php page?

ie: phpmyadmin or mysql front or ....

Posted: Sun Jul 11, 2004 12:07 pm
by feyd
post the table structures and some example data..

Posted: Sun Jul 11, 2004 12:44 pm
by Joe
images table
############
name
ID
description
price
image
imagedisplay
topicimage
orderimage
availability
term
tag


Orders Table
############
ID
date
fullname
address
city
state
zip
country
telephone
email
fax
payment
shipping
comments
pricing
carrots
freshmeat
apples
oranges
vegselection
sausages
crumpets
pancakes
matches
sample1
sample2
sample3

Everything is set as text however, ID and pricing and Integers. Just for a test I tried

Code: Select all

<?php
$link = mysql_connect($DBHost, $DBUser, $DBPass);
mysql_select_db($DBName) or die("Could not connect!" . mysql_error());
$query = "SELECT * FROM images AS photo, orders AS packages WHERE photo.name<>'Sample Pak' AND photo.name<>'Contact'";
$result = mysql_query($query) or die(mysql_error());

while (true)
{
$row = mysql_fetch_assoc($result);
if ($row == false) break;
$name = $row['name'];
$name = str_replace("-","", $name);

echo "<br><td><b>ID:</b> ".$row['ID']."</a></td><br>";	
echo "<td><b>Date:</b> ".$row['date']."</td><br>";

echo "<td><b>Payment Method:</b> ".$row['payment']."</td><br>";
echo "<td><b>Full Name:</b> ".$row['fullname']."</td><br>";
echo "<td><b>Email:</b> <a href='mailto:".$row['email']."'>".$row['email']."</a></td><br>";

echo "<td>".$name."</td><br>";

echo "<br><hr color='gray'>";
}
?>
Just to see what the name would come out as. I was resulted with an about 6 rows or so of ID 1 then ID 2 then ID 3 and so on... I am very confused here. Perhaps its my db structure?

Posted: Sun Jul 11, 2004 1:03 pm
by feyd
hmmm.. your original code was taking the data stored in image.name, so what's some values stored in the image table?

Posted: Sun Jul 11, 2004 1:16 pm
by Joe
In the name field of "images" which is where I am trying to get the date from there are the following fields:

apples
oranges
veg-selection
sausages
crumpets
pancakes
matches
Sample-PaK

Posted: Sun Jul 11, 2004 1:20 pm
by feyd
you list "Sample-PaK" yet your filtering with "Sample Pak", is this correct?

Posted: Sun Jul 11, 2004 1:24 pm
by Joe
Yes thats true indeed. I tried

$query = "SELECT * FROM images AS photo, orders AS packages WHERE photo.name<>'Sample Pak''";

As I posted in the one of the orginal productions. (No luck though) :(

Regards


Joe 8)

Posted: Sun Jul 11, 2004 1:31 pm
by feyd
what about

Code: Select all

<?php

$query = "SELECT * FROM `images` photo, `orders` packages WHERE photo.`name` != 'Sample-PaK'";

?>

Posted: Sun Jul 11, 2004 1:39 pm
by Joe
OK I tried the method you gave feyd and I was resulted with a repeated pattern of rows. By that I mean it had like 6 #1 ID's, 6 #2 ID's and so on...

Posted: Sun Jul 11, 2004 1:40 pm
by Joe
Just incase you require the code as it is now:

Code: Select all

<?php
$link = mysql_connect($DBHost, $DBUser, $DBPass);
mysql_select_db($DBName) or die("Could not connect!" . mysql_error());
$query = "SELECT * FROM `images` photo, `orders` packages WHERE photo.`name` != 'Sample-PaK'"; 
$result = mysql_query($query) or die(mysql_error());

while (true)
{
$row = mysql_fetch_assoc($result);
if ($row == false) break;

echo "<br><td><b>ID:</b> ".$row['ID']."</a></td><br>";	
echo "<td><b>Date:</b> ".$row['date']."</td><br>";

echo "<td><b>Payment Method:</b> ".$row['payment']."</td><br>";
echo "<td><b>Full Name:</b> ".$row['fullname']."</td><br>";
echo "<td><b>Email:</b> <a href='mailto:".$row['email']."'>".$row['email']."</a></td><br>";

$name = $row['name'];
$name = str_replace("-","", $name);

echo $row[$name]."<br>"; 

echo "<br><hr color='gray'>";
}
?>
Thanks

Posted: Sun Jul 11, 2004 1:46 pm
by feyd
since you are using fetch_assoc, you may be mangling the field values. Meaning, since both image and orders tables have ID as a field name, you may have mangled which one you are seeing..

Posted: Sun Jul 11, 2004 1:53 pm
by Joe
Im not too sure what you mean by which one am I seeing. It just shows continous records and about every third record down of each ID it shows the value 375 which is a cost in one of my db rows!

I also tried fetch_array with no luck, not thinking it would anyway!