Creating a table using PHP - help

[k4pil]

Hi there ppl!

I am trying to create a table by querying two database tables but am getting stuck.

e.g. One table is case(caseID,priority,created,subject)
The other is customer(custID,name,surname,phone)

Now my table - i want it to look something like this;

CASEID PRIORITY CREATED SUBJECT NAME SURNAME PHONE
1 4 1/2/2006 php jim bob 999
etc...

Normally you'd have a while loop, going through the database table then adding to the html table as $i incremented. But what do u do when there is two loops???

NE HELP???

thnks in advance

[elias]

Hi k4pil

What comes to mind is to have one while loop for the main table as you say, and then within that loop, when you come to the Name, Surname and PHone fields, you start another while loop, within the first one. In this way, for every "hit" in the first loop, the second loop fills out the remaining fields from the other table.

good luck

elias

[hitman6003]

What is the common field between them? In other words, how are you linking the customers to their case and vice versa?

case(caseID,priority,created,subject)
customer(custID,name,surname,phone, [!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]caseID[!--colorc--][/span][!--/colorc--])

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]customer[/color] [color=green]LEFT[/color] [color=green]JOIN[/color] case ON customer.caseID [color=orange]=[/color] case.caseID [!--sql2--][/div][!--sql3--]

[code]$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  echo "
  <tr>
    <td>CASEID  = $row[caseID]</td>
    <td>PRIORITY   = $row[pritority]</td>
    <td>CREATED   = $row[created]</td>
    <td>SUBJECT   = $row[subject]</td>
    <td>NAME   = $row[name]</td>
    <td>SURNAME   = $row[surname]</td>
    <td>PHONE  = $row[phone]</td>
  </tr>";
}[/code]

[k4pil]

The tables are actually like;

customer(custID, name, surname, email)
case(caseID, custID, date)

custID links the two together.i still can't get then query to work. I have tried;

SELECT *
FROM customer, case WHERE customer.ID = case.custID

and
SELECT * FROM case LEFT JOIN customer ON case.custID = case.custID

Still no luck. Read the manual and its still no good.

[hitman6003]

Reverse your tables in your sql statement:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]customer[/color] [color=green]LEFT[/color] [color=green]JOIN[/color] case ON customer.custID [color=orange]=[/color] case.custID
[!--sql2--][/div][!--sql3--]

[k4pil]

I have tried that and the error i get is;

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'case ON customer . custID = case . custID LIMIT 0, 30' at line 1


I don't understand why. Does anyone else???

[wickning1]

CASE is a reserved word. Try this:
[code]SELECT * FROM customer cu LEFT JOIN `case` c ON cu.custID = c.custID[/code]

[k4pil]

Its working.
I changed it slightly because i was getting a lot of null values output. I changed it to

$query = "SELECT * FROM customer, `case` WHERE customer.ID = `case`.custID";

Now in $query i get a data which contains all customers with case history. Now i need to get from that data those customer the user has searched for. Using a form i have the value $name, $surname etc.. how can i search these from $query??

Or do i need to do something with the $query i.e. "SELECT * FROM customer, `case` WHERE customer.ID = `case`.custID WHERE custID = $custID AND name = $name"

Hope this makes sense

Thanks in advance