Jump to content


Photo

Creating a table using PHP - help


  • Please log in to reply
7 replies to this topic

#1 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 25 February 2006 - 06:55 PM

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

#2 elias

elias
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationSpain

Posted 25 February 2006 - 07:08 PM

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

#3 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 25 February 2006 - 08:38 PM

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] * FROM customer LEFT JOIN case ON customer.caseID = case.caseID [!--sql2--][/div][!--sql3--]

$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>";
}


#4 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 26 February 2006 - 02:39 PM

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.

#5 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 26 February 2006 - 02:41 PM

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] * FROM customer LEFT JOIN case ON customer.custID = case.custID
[!--sql2--][/div][!--sql3--]

#6 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 26 February 2006 - 03:02 PM

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???

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 26 February 2006 - 03:30 PM

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


#8 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 26 February 2006 - 03:54 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users