[SOLVED] Query Problem!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

[SOLVED] Query Problem!

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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)
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

Did you try that query in a query window instead of using a php page?

ie: phpmyadmin or mysql front or ....
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post the table structures and some example data..
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmmm.. your original code was taking the data stored in image.name, so what's some values stored in the image table?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you list "Sample-PaK" yet your filtering with "Sample Pak", is this correct?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what about

Code: Select all

<?php

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

?>
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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...
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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!
Post Reply