[php & mySQL] Can't figure it out!

[barryman]

Hi guys, i really need your help on this one. I am at this moment an intern at a ICT company and I am supposed to make a website for online shopping. The actual website and coding is actually allready done by other interns, but i am supposed to do the styling and design. Also i need to expand some parts of the websites with php.

So lets get to my problem.

I am trying to get information out of 2 tables. The first one is tblorder and second one is tblcustomer. Here are the tables:

tblorder

pkorderid (primary key)

fkklantid

date

excTotal

incTotal

status

tblcustomer

pkklnr (primary key)

klname

....

username

password

In the browser, when it goes to the detail page, it passes the pkorderid through URL and there you get the info all about the order. BUT I am supposed to add also the information of the customers. So what I need to do is to equal fkklantid (tblorder) to pkklnr (tblcustomer) and show the information of the customer.

At this moment (after a lot of trial and error) I have this:

$query3="SELECT * FROM tblklant, tblorder WHERE tblklant.pkklnr = tblorder.fkklantid ORDER BY '" . $HTTP_GET_VARS["pkorderid"] . "'";

$resultq3 = mysql_query($query3) or die ("ERROR: " . mysql_error());

But this is not right. I get only 1 customer and everytime I select an other pkorderid, I still get the same customer. I just don't get it, how am I supposed to do the sql query here????????

[Tim Dorr Veteran]

You can try this, but I think it's equivalent:

$query = "SELECT * FROM tblklant LEFT JOIN tblorder ON tblklant.pkklnr = tblorder.fkklantid ORDER BY '" . $_GET["pkorderid"] . "'";

[barryman]

that didn't help...

other suggestions???????

[Tim Dorr Veteran]

Wait, a minute, shouldn't that be:

$query = "SELECT * FROM tblcustomer LEFT JOIN tblorder ON tblcustomer.pkklnr = tblorder.fkklantid ORDER BY '" . $_GET["pkorderid"] . "'";

tblcustomer instead of tblklant

[barryman]

oh no, thats not it. klant = customer in dutch. i translated it for you that you could easily understand. but srry, it got mixed up :unsure:

so i tried it, but it didn't work...

btw, i tried the query on phpMyAdmin, and it worked fine there! :s i just don't understand, why it is not selecting the right information. I think it has to do something with the id wich is passed through URL, the pkorderid. but what???

[Tim Dorr Veteran]

How do you loop through the recordset? Have you tried printing out the variables you use?

[barryman]

let me show you the whole code:

?$query="SELECT * FROM tblorder WHERE pkorderid='" . $HTTP_GET_VARS["pkorderid"] ."'";
 ?$resultq = mysql_query($query) or die ("FOUT: " . mysql_error());

 ?$query2="SELECT * FROM tblorderregels WHERE fkorderid='" . $HTTP_GET_VARS["pkorderid"] ."'";
 ?$resultq2 = mysql_query($query2) or die ("FOUT: " . mysql_error());

 ?$query3="SELECT * FROM tblklant, tblorder WHERE tblklant.pkklnr = tblorder.fkklantid ORDER BY '" . $HTTP_GET_VARS["pkorderid"] ."'";
 ?$resultq3 = mysql_query($query3) or die ("FOUT: " . mysql_error());

 ?
?>


<?php
while (list($pkorderid, $fkklantid, $datum, $excTotaal, $incTotaal, $status) = mysql_fetch_row($resultq))
	{
	$ud=$pkorderid;
	$vn=$fkklantid;
	$an=$datum;
	$un=$excTotaal;
	$pw=$incTotaal;
	$em=$status;
	}
?>

<?php
while (list($pkklnr, $klnaam, $anaam, $kladres, $pcode, $klwpl, $kltel, $klemail, $banknr) = mysql_fetch_row($resultq3))
	{
	$pkkln=$pkklnr;
	$klnaa=$klnaam;
	$anaa=$anaam;
	$kladre=$kladres;
	$pcod=$pcode;
	$klwp=$klwpl;
	$klte=$kltel;
	$klemai=$klemail;
	$bankn=$banknr;
	}
?>

and in the form I use this to print out the information:

<form action="<?php echo($_SERVER["PHP_SELF"]);?>" method="post">

<input type="hidden" name="bevestiging" value="1">

<input type="hidden" name="pkorderid" value="<?php echo($HTTP_GET_VARS["pkorderid"]);?>">

<table width="60%" border="0">

?<tr>

? ?<td>pkorderid ?: </td>

? ?<td><?php echo($ud);?></td>

?</tr>

?<tr>

? ?<td>Klant id:</td>

? ?<td><?php echo($vn);?></td>

?&lt<?php echo($ud);?>($ud);?> etc.

[the evn show]

This is unrelated to the original question, but I noticed you're using values right from $_GET without validation in your query. You might want to think about tweaking on that later. If for example your script runs on a server where magic_quotes are disabled then you run the risk of SQL injection attacks.

Googling for php/mysql security and escapequotes would be a good starting place.

[barryman]

oh, thx the evn show!! although i didn't made al those codes, an other intern did, and it is not my assignment to secure it better :)

and guys, i got great news!!!!! I GOT IT SOLVED!!!!!! :D :D :D

my teacher came by our company and i tolled him my problem and he just solved it, so easily!!!!!

the code needed an AND statement!!!

this is how it has to be like:

$query3="SELECT * FROM tblklant, tblorder WHERE tblklant.pkklnr = tblorder.fkklantid AND pkorderid = '" . $HTTP_GET_VARS["pkorderid"] ."'";

i love my teacher!! and you to guys!!! THX FOR YOUR HELP TIMDORR