[SOLVED] Not getting correct values??

[mickinell]

Okay, I am not sure what I've done wrong.

 

Here's what I am getting as output:

Ad Type: Personal: Senior Wish

 

Ad Size/Pricing Information: Personal: 1/4, picture ($40.00)

 

Sales Credit: None

 

Design: Designed by Yearbook Staff

 

Design Information: Provided.

 

Okay.  So the info in RED is supposed to be pulled from my mySQL database...but the data is wrong.

 

Here's the PHP for that part of the page:

$sql = "SELECT adtype.typeid, adtype.description, orders.adtype FROM adtype, orders WHERE adtype.typeid = orders.adtype";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$description = $row['description'];
}
$output .= "Ad Type:  $description<br><br>";
$sql = "SELECT adsize.sizeid, adsize.description, orders.adsize FROM adsize, orders WHERE adsize.sizeid = orders.adsize";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$addesc = $row['description'];
}
$output .= "Ad Size/Pricing Information:  $addesc<br><br>";

$sql = "SELECT seller.sellerid, seller.name, orders.student FROM seller, orders WHERE seller.sellerid = orders.student";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$student = $row['name'];
}

$output .= "Sales Credit:  $student<br><br>";

$sql = "SELECT design.designid, design.description, orders.design FROM design, orders WHERE design.designid = orders.design";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$design = $row['description'];
}


$output .= "Design:  $design<br><br>";

$sql = "SELECT designsugg, adfor FROM orders WHERE orderno = $orderno";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$designsugg = $row['designsugg'];
	$adfor = $row['adfor'];
}

$output .= "Design Information:  $designsugg<br><br>
Ad Recipient:  $adfor";
print $output;

 

Now, to explain further...I have several tables that I'm trying to draw information from, and that's where the problem comes in.

 

The table "orders" records the ad size, type, and seller as an id number.  There are then tables for each of those three tables (adsize, adtype, and seller).

 

The problem is that it's not selectin gthe right information from those tables.  I have checked the row for the invoice displayed above, and the data it's returning is not right.  It's giving me the first row in the adsize, adtype, and seller tables instead of the correct answers.    I know I can get the correct answers, though, because when I add the original variable in, I get it:

 

Ad Type: Personal: Senior Wish (3)

 

The (3) is ($adtype) in my code, and that's the correct ad size, but it's not joining the tables properly to report the ad size that goes with the #3 id--it's using the first one.

 

typeid | description

    1    | Personal:  Senior Wish

    2    | Personal:  Other Student/Staff

    3    | Business Advertisement

 

I'm sorry if this doesn't make sense...this is one of my first solo attempts and I'm 100% self taught.  I'm sure it's something simple, but I have no clue.  Thoughts?

 

 

[jon23d]

If you have your relationships set up properly in the database, then try using one query that pulls it all.  Joins are great, and as long as your keys are set up, referential integrity never becomes an issue.

 

http://www.w3schools.com/Sql/sql_join.asp

 

 

[mickinell]

Well, I just found w3 schools, actually, and I tried my first join.  But I'm still having an error.

 

Ad Type: Business Advertisement (3)

 

Ad Size/Pricing Information: Business: Both Endsheets ($750.00) (5)

 

Sales Credit: Jade Gill

 

The red highlight is the 11th (last) value in that table, but the ID in parentheses is correct--5.  I managed to get the other two working and I don't understand why this would be any different.  The tables are set up the exact same, just called adsize instead of adtype. 

 

$sql = "SELECT adtype.typeid, adtype.description FROM adtype LEFT JOIN orders ON adtype.typeid=orders.adtype";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$description = $row['description'];
}
$output .= "Ad Type:  $description ($adtype)<br><br>";
$sql = "SELECT adsize.sizeid, adsize.description FROM adsize LEFT JOIN orders ON adsize.sizeid=orders.adsize";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$addesc = $row['description'];
}
$output .= "Ad Size/Pricing Information:  $addesc ($adsize)<br><br>";

$sql = "SELECT seller.sellerid, seller.name FROM seller LEFT JOIN orders ON seller.sellerid = orders.student";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$student = $row['name'];
}

$output .= "Sales Credit:  $student<br><br>";

 

 

[redarrow]

why are you hiding the database error's?

 

have you cheeked to see if the echoed select query is correct?

[mvfreelance]

not trying any tricks here, but post the structure of ur tables and I'll give u the right query.

 

[Psycho]

FIRST: You should really read a tutorial on how to do JOINS in your queries. You should be able to get all the information you need in ONE query. Your current process is terribly inefficient.

 

SECOND: The WHERE caluse in your first query doesn't include a specific record, so it is getting ALL records. Then after you run your query you iterate through every record redefining $description on each iteration. But, you don't use $description in your loop, so what happens is that when the loop finished you are left with the description for the LAST record. I think you probably want to be specifying a specific record within the WHERE clause using an order ID or something similar. You appear to have this same problem on all of your queries.

 

Give this a try. I had to make a lot of changes so I can't guarantee it will work - especially since I don't have your database to test against. Also, I took out a lot of the variables you were SELECTing in the query because I didn't see that you were actually using them.

 

<?php

$query = "SELECT adtype.description as adDesc,
                 adsize.description as adSizeDesc,
                 seller.name as student,
                 design.description as designDesc,
                 orders.designsugg as designsugg,
                 orders.adfor as adfor

          FROM orders
            JOIN adtype ON orders.adtype  = adtype.typeid
            JOIN adsize ON orders.adsize  = adsize.sizeid
            JOIN seller ON orders.student = seller.sellerid
            JOIN design ON orders.design  = design.designid

          WHERE orders.orderno = $orderno";


$result = @mysql_query($sql,$connection) or die(mysql_error());

$output .= "Ad Type:  {$result['adDesc']}<br /><br /> ";
$output .= "Ad Size/Pricing Information:  {$result['adSizeDesc']}<br /><br /> ";
$output .= "Sales Credit:  {$result['student']}<br /><br /> ";
$output .= "Design:  {$result['designDesc']}<br /><br /> ";
$output .= "Design Information:  {$result['designsugg']}<br /><br /> ";
$output .= "Ad Recipient:  {$result['adfor']} ";

print $output;

?>

[mickinell]

Okay, I am an idiot.  It's not fixed, it just so happens that now it's giving me the last values in all of those tables.  Argh.

 

The orders table is a long table with lots of information, but it has

 

adtype | adsize | seller

 

which store the id numbers for each of those.

 

The adtype table has

 

typeid | description

 

Adsize is sizeid | description

 

And the seller table has sellerid | name.

 

I know that I can do a big large complicated join, but since I can't get a simple one to work...

[mickinell]

mjdamato:  thank you for the reply.  I'm trying to understand what you mean.  I'm sure it has to be frustrating when you're dealing with someone with piecemeal knowledge!

 

I'm working with what you posted, but I'm not getting any values back at all...so I need to play with that some.  I think I can use that to get me in the right direction, though.

 

The main reason I was doing it in bits is because it's easier, not knowing what you're doing, to isolate things done in pieces.  I know that's not the most efficient, but I am only using this on a tiny personal scale.

[mvfreelance]

don't wanna sound rude, but  If you wanna get help you should help the folks that are trying to help you first...

 

ok, accordinly to u , and I quote below, table orders has 3 fields (adtype | adsize | seller) ...

WHY are you querying "student" and "design" there then??

 

IF u give better details, u might get a better answer.

 

 

 

Okay, I am an idiot.  It's not fixed, it just so happens that now it's giving me the last values in all of those tables.  Argh.

 

The orders table is a long table with lots of information, but it has

 

adtype | adsize | seller

 

which store the id numbers for each of those.

 

The adtype table has

 

typeid | description

 

Adsize is sizeid | description

 

And the seller table has sellerid | name.

 

I know that I can do a big large complicated join, but since I can't get a simple one to work...

[mickinell]

don't wanna sound rude, but  If you wanna get help you should help the folks that are trying to help you first...

 

ok, accordinly to u , and I quote below, table orders has 3 fields (adtype | adsize | seller) ...

WHY are you querying "student" and "design" there then??  EDIT --it has many fields.  These are the three I am trying to reference.

 

IF u give better details, u might get a better answer.

 

 

 

Okay, I am an idiot.  It's not fixed, it just so happens that now it's giving me the last values in all of those tables.  Argh.

 

The orders table is a long table with lots of information, but it has

 

adtype | adsize | seller

 

which store the id numbers for each of those.

 

The adtype table has

 

typeid | description

 

Adsize is sizeid | description

 

And the seller table has sellerid | name.

 

I know that I can do a big large complicated join, but since I can't get a simple one to work...

 

I am getting the correct information from those queries...that's why I wasn't worried about those.  That information is part of the order table.

 

CREATE TABLE IF NOT EXISTS `orders` (
  `orderno` int(11) NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `name` varchar(200) NOT NULL default '',
  `business` text NOT NULL,
  `staddress` text NOT NULL,
  `cityst` text NOT NULL,
  `phone` varchar(200) NOT NULL default '',
  `fax` varchar(200) NOT NULL default '',
  `email` text NOT NULL,
  `adtype` varchar(200) NOT NULL default '1',
  `adsize` varchar(200) NOT NULL default '1',
  `buybook` tinyint(4) NOT NULL default '0',
  `donatebook` tinyint(4) NOT NULL default '0',
  `nobook` tinyint(4) NOT NULL default '0',
  `student` text NOT NULL,
  `design` varchar(200) NOT NULL default '1',
  `designsugg` text NOT NULL,
  `adfor` text NOT NULL,
  `status` varchar(200) NOT NULL default 'Awaiting Payment',
  `page` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`orderno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=90031 

The only three I am having a problem with are the ones listed above. 

[redarrow]

just post all the database structure or a screen shot.

 

if we can see the information from the database your trying to join with then we can  help.

[mickinell]

Not necessary; I've figured it out.

 

I needed to add where orders.orderno = $orderno.

 

Thanks for the help.

[redarrow]

post you fixed results for others please....

[Psycho]

Not necessary; I've figured it out.

 

I needed to add where orders.orderno = $orderno.

 

Thanks for the help.

 

Right, exactly as I pointed out. I really hope you are not using the original code with that additional WHERE clause added. I wrote a lot of code and was unable to test it, so I expect there may have been a few errors, but the logic is sound and is the right way to do this.

 

I suggest you start with one table (the orders table) to get the information you need from that table. Then add just one table using a JOIN to get the information you need from that table, etc.