php code problem

[novice2]

I have two tables
table 1
idn ......1..........2
name....john......mary

table2
id......1...........2.........3...........4
colors..white......red.......blue.......green
idn......1..........2.........1...........1

I have my query SELECT * FROM table1,table2 WHERE table1.idn=table2.idn
and I want to get results like this:

name...........colors
--------|-------------
john....|...white
........|...blue
........|...green
mary....|...red

So if anyone can help me with php code to achive this result?

[diafol]

Which table do you want to the the main one - colours (2)? Your sql will need to reflect whether you want a LEFT, RIGHT or INNER JOIN. At the moment you have an inner (implicit) join. You will only return records which have a common 'idn'.

WRT php -
1. Use mysql_query() to run the query.
2. Use while with mysql_fetch_array() to loop through all the records.

3. Your loop should echo the html with the values from the recordset:

<tr><td>...name variable...</td><td>...color variable...</td></tr>

Obviously you'll echo the first and last bits of your html table before and after the while loop. Hope that gives you an idea, I'm loathe to write the code out in full.

[novice2]

are you saying to do this:

echo "<table>";
echo "<tr> <th>name</th> <th>colour</th></tr>";

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo "<tr><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "<td></tr>";
}
Will this return the results from two diferent tables and get this?

name...........colors
--------|-------------
john....|...white
........|...blue
........|...green
mary....|...red

[rixius]

SELECT table1.*, table2.colors FROM table1, table2 WHERE table2.idn = table1.idn

Should produce something like:

idn | name | colors
----+------+--------
1   | John | white
1   | John | blue
1   | John | green
2   | Mary | red

[diafol]

> are you saying to do this:

Yes, you're a quick study N2, although the last line in your loop should be:

echo "</td></tr>";

and you need to close off the </table> after the loop.

Personally, I'd output like this:

echo "<tr><td>{$row['name']}</td><td>{$row['colour']}</td></tr>";

Each to his own though.

[novice2]

Ok, got the data like this:

idn | name | colors
----+------+--------
1 | John | white
1 | John | blue
1 | John | green
2 | Mary | red
And can I get a result like this?
1. name....|......colors
2. --------+-------------
3. john....+...white
4. ........+...blue
5. ........+...green
6. mary....+...red
maby with a separate queries and nested while loop or is there a way to achive this?

[diafol]

Just make a counter and use it in the loop:

$x = 1;
while(....){
  echo "<tr><td>{$x}.</td><td>{$row['name']}</td><td>{$row['colour']}</td></tr>";
  $x++;
}

[novice2]

wanted to say can I get a result where the name john is displayed only once and the colours, and the name mary only once and the colours. Can I make one query and a while loop for the name column(table1) and other query for the colours from the other table and nested while loop. Is there a way to do this?
Thanks!

[diafol]

Oh OK, I didn't realise that's what the gaps were. Well I suppose you could do some magic with SQL, but perhaps the best way would be with php so that you don't slow things down too much:

$username =""; //initialize username to force first name to show immediately
while(...){
  echo "<tr>";
  if($username != $row['name']){
    echo "<td>{$row['name']}</td>"; //show if current username different to new record name
  }else{
    echo "<td> </td>"; //don't display name if current username SAME as new record name 
  }
  $username = $row['name']; //set the current username to the new record name
  echo "<td>{$row['color']}</td></tr>";
}

BTW, remember to set an ORDER BY clause in the SQL to order by 'name' and then 'color'

[novice2]

AWESOME THANKS! this is solved

[novice2]

What is the magic with SQL ?

[diafol]

The magic includes the thought behind producing a succinct and *fast* piece of SQL. As the previous example seems to work for you, I shall conserve my god-like mind for more trivial matters! :) In other words, I'm not going there, I'll get a nosebleed.

[novice2]

OK one more question
With these lines of code
while (......){
echo "<tr><td>{$x}.</td><td>{$row}</td><td>{$row}</td></tr>";
}
and SELECT DISTINCT name, colours FROM............
Why I get repeating results
they are selected as primary key

[diafol]

sorry don't follow What's the repeating result?

[novice2]

tought SELECT DISTINCT doesnt let repeating rows
the name john is displayed 3 times

    idn | name | colors
     ----+------+--------
     1 | John | white
     1 | John | blue
     1 | John | green
     2 | Mary | red

when I use these lines of code

while (......){
echo "<tr><td>{$x}.</td><td>{$row['name']}</td><td>{$row['colour']}</td></tr>";
}
and SELECT DISTINCT name, colour FROM............

[diafol]

It doesn't allow repeating rows, but the thing is, you don't have any repeating rows as the values of your fields (name, color) are different. If you're querying more than one field, the distinct will consider all those fields and search for perfect matches in ALL of them before ignoring them.

[novice2]

So,I have to use php and I cant do this with SQL queries or???

[diafol]

You could research GROUP BY.

[novice2]

GROUP BY name doesnt display all colours, only one or with colour option the result is as it was

[diafol]

I think you'll still need php to filter out duplicate names if you want:

name1 colour1
      colour2
      colour3
name2 colour3

etc

For what it's worth, I think php conditional in the loop would be quicker than cobbling a horrible pure SQL solution. DBs are designed to retrieve data. Using them to do all sorts of jiggery pokery will be slow. Granted they can do basic format and calculate.

[novice2]

OK THANKS