Php adding mysql from 2 tables data

Question

Im having a problem retrieving some data from 2 tables and then displaying the output into one area.

This is the code for the retrieve I have

$select_users = "SELECT CarID, Model, Year, Price, ManuName  FROM Vehicle, Manufacturer";
        //run query

        $result = mysql_query($select_users);

This is the code for the output

<?php
while ($user = mysql_fetch_array($result)){
$user_row = sprintf(
"<li><a href='select_veh.php?CarID=%d' class='mainnavstyle'>%s %s %s %s</a></li>", 
    $user['CarID'], 
    $user['ManuName'], 
    $user['Model'], 
    $user['Year'], 
    $user['Price']);
                echo $user_row;

           }
    ?>

I cant seem to get 'ManuName' to show properly as its from a different table. At the moment with this code im getting duplicate results:

honda Civic 2000 $15,000

honda Corolla 1991 $5,000

Toyota Civic 2000 $15,000

Toyota Corolla 1991 $5,000

Instead of just

Honda Civic 2000 $15,000
Toyota Corolla 1991 $5,000

Any help would be greatly appreciated thanks

It is better to usr join query between two table

Saty
– Saty

2015-03-30 06:13:23 +00:00
Commented Mar 30, 2015 at 6:13 — Saty
– Saty, Commented Mar 30, 2015 at 6:13

pala_ · Accepted Answer · 2015-03-30 06:26:32Z

3

You are performing a cross join, which matches every row of one table, with every row of the other. This is almost certainly what you don't want. You instead need to limit how the tables join together, to do this, your tables Vehicle and Manufacturer need to be related to each other, as such:

CREATE TABLE manufacturer (
   id integer primary key auto_increment,
   name varchar(255)
  );

CREATE TABLE vehicle (
  id integer primary key auto_increment,
  name varchar(255),
  year integer,
  price double,
  manufacturer_id integer not null,
  FOREIGN KEY(manufacturer_id) REFERENCES manufacturer(id)
);

You can then get the information you want like this:

SELECT m.name, v.name, v.year, v.price FROM vehicle v
    INNER JOIN manufacturer m
    ON v.manufacturer_id = m.id

You can see this in action here: http://sqlfiddle.com/#!9/5c1f2/1

edited Mar 30, 2015 at 6:26

answered Mar 30, 2015 at 6:21

pala_

9,0101 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marko_b123 · Accepted Answer · 2015-03-30 06:51:34Z

0

In vehicle table I would have the foreign key of manufacturer, and than query would be something like:

SELECT Vehicle.CarID, Vehicle.Model, Vehicle.Year, Vehicle.Price, Vehicle.ManuName, Manufacturer.name INNER JOIN Manufacturer ON(Vehicle.manufacturerId = Manufacturer.id) FROM Vehicle

edited Mar 30, 2015 at 6:51

answered Mar 30, 2015 at 6:18

marko_b123

3102 silver badges17 bronze badges

Comments

hass. · Accepted Answer · 2015-03-30 07:06:52Z

0

you could do the sql query together, using unions or joins

alternatively, this is what you can do too:

$sql1= "SELECT something FROM tbl WHERE column='".query."'";
$result1= $con->query($sql1);
if($result1->num_rows > 0){
    while($row = $result->fetch_assoc()) {     
        $rowans = $row["row"];

        $sql2= "SELECT * FROM tbl2 WHERE column2= '".something."'"; 
        $gettingRows2= $con->query($sql);
        if($gettingRows2->num_rows > 0){
            while($anotherrow= $sql2->fetch_assoc()) {
                $sql2Row= $anotherrow["row2"];
            }   
        }
    }
}
        echo $rowans." ".$sql2Row;

answered Mar 30, 2015 at 7:06

hass.

105 bronze badges

Collectives™ on Stack Overflow

Php adding mysql from 2 tables data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related