multi table if then

[littlegeek]

hello all, i have been sitting here googleing and googling but i can't seem to find what im looking for.

 

i am tring to create a personal site that lets me note services i do for my cars. I have multiple tables (cars, color, mfg, ...) in the main table cars i insted of putting the color black i put and int(11) of 1 which is suppose to "if statement" to the table colors and produce the correct color. same for mfg. although nothing i have tried has helped i always just have the else of the if echoed out.

 

i have heard of union and join left right i am so unsure of what is need any insite would be useful.

 

thanks in advance.

[denno020]

You're not going to get any useful help without showing some code. Also, why would you need a whole table for colours? Is there a certain reason for this? You could just make colour an ENUM column of the cars table.

 

But if you're content on using multiple tables, then post your code.

 

Denno

[MatthewJ]

Not making much sense, but as a rough guess.. I am sassuming you are looking for something like this.

 

SELECT table1.carType, table1.carYear, table2.color
FROM table1
JOIN table2
ON table1.colorID = table2.colorID

 

That would select the car type and car year from table one... and then will join the car color from table two based on the matching number.

[RussellReal]

You could pull it off like this..

SELECT id, color As (SELECT colorName FROM colors WHERE colors.id = colorId), manufacturer As (SELECT manuName FROM mfg WHERE mfg.id = manufacturerId) FROM cars WHERE id = '1'

 

[littlegeek]

As for table structure i have the folloing tables:

 

cars => id nick make model year vin color plate purchdate  purchprice pic

color => id shade

maintenance=> id description sdate mileage cost shop invoice

make => id mfg

note => id cid note

part => id part type service

service => id type

shop => id name

 

so if i have a echoed table and i want to list all the cars from table cars i would like a result of:

 

Jenna Jetta |

Volkswagen |

Jetta GL |

1996 |

blah blah |

black |

blah |

xx/xx/xxxx |

$2000.00 |

pic

 

but the table cars has data:

Jenna Jetta |

1 |

1996 |

blah blah |

1 |

blah |

xx/xx/xxxx |

2000.00 |

directory

 

so i ask about how to if the "1's"

 

 

//if (cars.color==color.id)

//{echo 'color.shade';

//else

//echo 'cars.color';

//}

 

same sort of thing is needed for mfg table for make in cars table.

 

 

srry i didnt include this earlier.

 

hope this helps you help me

 

thanks for the quick responses and i hope that i am not misunderstanding what was already posted in response.

thanks again

 

littlegeek

[RussellReal]

see my reply

[littlegeek]

<?php
//get data
$mfg = mysql_query("SELECT id, color As (SELECT shade FROM color WHERE color.id = colorId), make As (SELECT make FROM make WHERE make.id = makeId) FROM cars WHERE make = '1'");
echo "<table border=\"1\" align=\"center\">";
echo "<tr><th>Name</th><th>MFG</th><th>Model</th><th>Year</th></tr>";
while ($mic = mysql_fetch_assoc($mfg))
{
$name = $mic['nick'];
$make = $mic['make'];
$model = $mic['model'];
$year =$mic['year'];

echo "<tr>";
echo "<td>";
echo $name;
echo "</td>";
echo "<td>";
echo $make;
echo "</td>";	
echo "<td>";
echo $model;
echo "</td>";	
echo "<td>";
echo $year;
echo "</td></tr>";

}
echo "</table>";

?>

 

 

given output on execution =>

 

 

<table border="1" align="center"><tr><th>Name</th><th>MFG</th><th>Model</th><th>Year</th></tr><br />

<b>Warning</b>:  mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in <b>/var/www/tjc/list.php</b> on line <b>10</b><br />

</table>

 

 

 

 

[RussellReal]

you aren't changing the query I've given you much...

 

mysql_query("SELECT id, shade As (SELECT shade FROM color WHERE color.id = cars.color), carMake As (SELECT make FROM mfg WHERE make.id = cars.make) FROM cars WHERE make = '1'");

[littlegeek]

hello again i have reinserted what you have given me @RussellReal and i now have an error that i don't understand because i think everything is correct.

 

<?php
//get data
$mfg = mysql_query("SELECT id, shade As (SELECT shade FROM color WHERE color.id = cars.color), carMake As (SELECT make FROM mfg WHERE make.id = cars.make) FROM cars WHERE make = '1'");
echo "<table border=\"1\" align=\"center\">";
echo "<tr><th>Name</th><th>MFG</th><th>Model</th><th>Year</th></tr>";
//line 10 =>while ($mic = mysql_fetch_assoc($mfg))
{
$name = $mic['nick'];
$make = $mic['make'];
$model = $mic['model'];
$year =$mic['year'];

echo "<tr>";
echo "<td>";
echo $name;
echo "</td>";
echo "<td>";
echo $make;
echo "</td>";	
echo "<td>";
echo $model;
echo "</td>";	
echo "<td>";
echo $year;
echo "</td></tr>";

}
echo "</table>";

?>

the error i get is Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/tjc/list.php on line 10

 

p.s. srry for the long silence i had to go to work( stupid job lol)

 

 

p.p.s thanks for all the help i really cant thank you enough

[RussellReal]

$mfg = mysql_query("SELECT a.*, b.shade, c.make FROM cars a JOIN color b ON (b.id = a.color) JOIN mfg c ON c.id = a.make WHERE a.make = '1'") or die(mysql_error());

 

 

try this, and if I misnamed a field please try to correct it, but this should tell you where the problem is should you encounter one

[littlegeek]

WORKS GREAT for mfg in table if there is a static variable i.e 1 so i tried this

while($det = mysql_fetch_assoc($cool))
{   
$makeset = $det['make'];

$i = $makeset;
}
$mfg = mysql_query("SELECT a.*, b.shade, c.make FROM cars a JOIN color b ON (b.id = a.color) JOIN mfg c ON c.id = a.make WHERE a.make = $i") or die(mysql_error());

 

 

didn't work but was worth a try.

 

then i tried this

 

function makeset()
{while($det = mysql_fetch_assoc($cool))
{   
$makeset1 = $det['make'];

$i = $makeset;
}
}
$cool = mysql_query("Select * FROM cars Where id > 0");
$mfg = mysql_query("SELECT a.*, b.shade, c.make FROM cars a JOIN color b ON (b.id = a.color) JOIN mfg c ON c.id = a.make WHERE a.make = makeset()") or die(mysql_error());

 

resulted in telling me this contradictory error => FUNCTION vehicel.makeset does not exist!!!

 

 

i am so thankful for you help this is really exciting for me to see this work correctly

 

PS the only thing that doesn't work is the color haven worked on that part yet but I'm sure its similar to mfg code so no biggie

 

 

 

so you have it here is full code that !!works!!

 

 

<?php 
include 'dbc.php';
page_protect();
?>
<?php
$cool = mysql_query("Select * FROM cars Where id > 0");
$mfg = mysql_query("SELECT a.*, b.shade, c.make FROM cars a JOIN color b ON (b.id = a.color) JOIN mfg c ON c.id = a.make WHERE a.make = '1'") or die(mysql_error());
//text='#FFFFFF'>
echo "<body ";
echo "<table border=\"1\" align=\"center\">";
echo "<tr><th>Name</th><th>VIN</th><th colspan='10'>Options</th></tr>";
while ($mic = mysql_fetch_assoc($cool))
{while($mic2 = mysql_fetch_assoc($mfg))
{
$name = $mic['nick'];
$vin = $mic['vin'];	
$make = $mic2['make'];
$model = $mic['model'];	
$year = $mic['year'];	
$color = $mic2['color'];	
$plate = $mic['plate'];	
$purchdate = $mic['puchdate'];	
$purchprice = $mic['puchprice'];
echo "<tr>";
echo "<td>";
echo "<a href='car.php'>$name</a>";
echo "</td>";
echo "<td>";
echo $vin;
echo "</td>";	
echo "<td>";
echo $make;
echo "</td>";
echo "<td>";
echo $model;
echo "</td>";
echo "<td>";
echo $year;
echo "</td>";
echo "<td>";
echo $color;
echo "</td>";
echo "<td>";
echo $plate;
echo "</td>";
echo "<td>";
echo $purchdate;
echo "</td>";
echo "<td>";
echo $purchprice;
echo "</td>";
echo "<td>";
echo "<a href='parts.php'>Parts</a>";
echo "</td>";	
echo "<td>";
echo "<a href='list.php'>Maintenance</a>";
echo "</td>";
echo "<td>";
echo "<a href='notes.php'>Notes</a>";
echo "</td></tr>";

}
}
echo "</table>";
?>

 

[RussellReal]

Glad to have helped

[littlegeek]

my brain is numb and i just got home from work everything is superb but the color and IDK why i keep looking over and over the query and it should work but i still get a i,1,2,3 etc...

 

Why doesn't this work

 

 

 

$mfg = mysql_query("SELECT a.*, b.shade, c.make FROM cars a JOIN color b ON b.id = a.color JOIN mfg c ON c.id = a.make WHERE a.make = a.make") or die(mysql_error());

echo "<body text='#FFFFFF'>";
echo "<table border=\"1\" align=\"center\">";
echo "<tr><th>Name</th><th>VIN</th><th colspan='10'>Options</th></tr>";
while ($mic = mysql_fetch_assoc($mfg))
{
$name = $mic['nick'];
$vin = $mic['vin'];	
$make = $mic['make'];
$model = $mic['model'];	
$year = $mic['year'];	
$color = $mic['color'];	
$plate = $mic['plate'];	
$purchdate = $mic['puchdate'];	
$purchprice = $mic['puchprice'];
echo "<tr>";
echo "<td>";
echo "<a href='car.php'>$name</a>";
echo "</td>";
echo "<td>";
echo $vin;
echo "</td>";	
echo "<td>";
echo $make;
echo "</td>";
echo "<td>";
echo $model;
echo "</td>";
echo "<td>";
echo $year;
echo "</td>";
echo "<td>";
echo $color;
echo "</td>";
echo "<td>";
echo $plate;
echo "</td>";
echo "<td>";
echo $purchdate;
echo "</td>";
echo "<td>";
echo "$",$purchprice;
echo "</td>";
echo "<td>";
echo "<a href='parts.php'>Parts</a>";
echo "</td>";	
echo "<td>";
echo "<a href='list.php'>Maintenance</a>";
echo "</td>";
echo "<td>";
echo "<a href='notes.php'>Notes</a>";
echo "</td></tr>";

}
echo "</table>";

[Muddy_Funster]

I have re-writen youre code a little - have a go with this, it's my best guess at what you are looking for (replaces everything within the second <?php ?>):

<?php
if (!isset($_POST['filter']){
$filter = 1;
}else{
$filter = mysql_real_escape_string($_POST['filter']);
}
echo'<form action="#" method="POST"><label>Search By Model No. (Default is 1) </label><input type="text" name="filter" />';
$mfg = mysql_query('SELECT 
  cars.nick, cars.vin, cars.model, cars.year, cars.color, cars.plate, cars.purchdate, color.shade, color.id, mfg.make 
FROM 
  cars INNER JOIN color ON (color.id = cars.color) INNER JOIN mfg ON mfg.id = cars.make 
WHERE cars.make = '.$filter.')' 
or die($mfg.'<br>~-~-~-~-~-~<br>Caused an error when running.  Error is -- <br>'.mysql_error());
echo "<body ";
echo "<table border=\"1\" align=\"center\">";
echo "<tr><th>Name</th><th>VIN</th><th colspan='10'>Options</th></tr>";
{while($mic = mysql_fetch_assoc($mfg))
{
$name = $mic['nick'];
$vin = $mic['vin'];	
$make = $mic['make'];
$model = $mic['model'];	
$year = $mic['year'];	
$color = $mic['color'];
          $colorID = $mic['id'];
          $shade = $mic['shade'];
        $plate = $mic['plate'];	
$purchdate = $mic['puchdate'];	
$purchprice = $mic['puchprice'];
if ($color == $colorID){
  $colorOut = $shade;
  }else{
  $colorOut = $color;
  }
echo "<tr>";
echo "<td><a href='car.php'>$name</a></td><td>$vin</td><td>$make</td><td>$model</td>";
echo "<td>$year</td><td>$colorOut</td><td>$plate</td><td>$purchdate</td><td>$purchprice</td>";
echo "<td><a href='parts.php'>Parts</a></td><td><a href='list.php'>Maintenance</a></td>";
  echo "<td><a href='notes.php'>Notes</a></td></tr>";
}
echo "</table>";
?>

[RussellReal]

the MySQL Query I have given you.. there are 3 parts to it..

 

table a: your Cars table..

table b: your Colors table..

table c: your MFG table..

 

yes it will still pull a.color (cars.color) which will be an integer, but b.shade should be the name of that color?

 

if in the database that isn't how this will work, than you need to change the field names, to reflect what you want to happen...

[littlegeek]

@Russellreal  everything is correct i have triple checked and yet all i get is the int no color name i have no clue as to why but the int for the mfg is replaced with Pontiac or Volkswagen perplexing

[Muddy_Funster]

and what happens with the code I posted?