mrdeath4 mrdeath4 - 2 years ago 63
MySQL Question

Make a table with a MySQL query with multiple values in some fields

I'v got a probled with a MySQL query or my logic.

I need to make a html table with values from my DATABASE. But in some fields of my table can be a multiple value. How should I make my table? Can I do it in one query or should I do a 2nd query after the table is finished?

Here are the tables:

offers:
id | rid | name
------------------------------
1 | 1234 | mary
2 | 1235 | john
3 | 5342 | liam

geo_in_off:
offer_id | geo_id
------------------------------
1 | 1
1 | 2
3 | 3


Some offers can Have 2 geo's. So I need a htm ltable that should looks like:

No | rid | name | geo
----------------------------------
1 | 1234 | mary | 1, 2


But i get a table with dublicates looking like:

No | rid | name | geo
----------------------------------
1 | 1234 | mary | 1
2 | 1234 | mary | 2


How can I fix it?

Here is my code:

require('../config.php');
$sql ="SELECT * FROM `offers` LEFT JOIN `geo_in_off` ON `geo_in_off`.`offer_id` = `offers`.`id` ORDER BY `offers`.`rid`";
$result = mysql_query($sql) or die(mysql_error());

while ($row=mysql_fetch_assoc($result)) {
echo "<tr><td>" . $i . "</td><td class=\"editable\">" . $row['rid'] . "</td><td class=\"editable\">" . $row['name'] . "</td><td>" .$row['geo_id'] . "</td></tr>" ;
$i++;
}


here is the config.php:

$db_name = "dbname";
$db_user = "user";
$db_pass = "pass";
$db_connect = mysql_connect('localhost', $db_user, $db_pass);
$selected = mysql_select_db($db_name, $db_connect);
@mysql_query('set character_set_results="utf8"');
@mysql_query('set collation_connection="utf8_general_ci"');
@mysql_query('set character_set_client="utf8"');

Answer Source

1st : use group_concat and group by clause

GROUP_CONCAT() will returns a string with concatenated non-NULL value from a group

SELECT  offers.rid,offers.name,group_concat(geo_in_off.geo_id) 
FROM `offers` 
LEFT JOIN `geo_in_off` 
ON `geo_in_off`.`offer_id` = `offers`.`id` 
GROUP BY `offers`.`rid`,offers.name  
ORDER BY `offers`.`rid`

Note : GROUP_CONCAT functions Default separator of is comma(,) . also able to use custom one .

2nd : mysql_* is deprecated try to use mysqli_*

3rd : Also try to use pdo or prepared statement

PHP :

require('../config.php');


$sql ="SELECT  `offers`.`rid` as Rid ,`offers`.`name` as Name ,group_concat(geo_in_off.geo) as geos 
        FROM `offers` 
        LEFT JOIN `geo_in_off` 
        ON `geo_in_off`.`offer_id` = `offers`.`id` 
        GROUP BY `offers`.`rid`,`offers`.`name`  
        ORDER BY `offers`.`rid`";

 $stmt = $conn->prepare($sql);
 $stmt->execute();
 $get_result =$stmt->get_result();
 $row_count= $get_result->num_rows;

 if($row_count>0){  

    echo "<table><thead><tr><th>No</th><th>Rid</th><th>name</th><th>goes</th></tr></thead><tbody>";
    $i=0;
    while ($row=$get_result->fetch_assoc()) {

        echo "<tr><td>" . $i . "</td><td class=\"editable\">" . $row['Rid'] . "</td><td class=\"editable\">" . $row['Name'] . "</td><td>" .$row['geos'] . "</td></tr>";
        $i++;

    }

    echo "</tbody></table>";

 }

Config.php

$db_name = "dbname";
$db_user = "user";
$db_pass = "pass";
$conn = mysqli_connect('localhost', $db_user, $db_pass,$db_name) or die('error'.mysqli_connect_error());
mysqli_query($conn,'set character_set_results="utf8"');
mysqli_query($conn,'set collation_connection="utf8_general_ci"');
mysqli_query($conn,'set character_set_client="utf8"');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download