Jump to content

Archived

This topic is now archived and is closed to further replies.

k4pil

Creating a table using PHP - help

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
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???

Share this post


Link to post
Share on other sites
CASE is a reserved word. Try this:
[code]SELECT * FROM customer cu LEFT JOIN `case` c ON cu.custID = c.custID[/code]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.