Jump to content

Creating a table using PHP - help


k4pil

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
Link to comment
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
Link to comment
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]
Link to comment
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.
Link to comment
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--]
Link to comment
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???
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.