- by CT
I am working on an Asset Database problem using PHP / MySQL.
In this script I would like to search my assets by an asset id and have it return all related fields.
First I query the database asset table and find the asset's type. Then depending on the type I run 1 of 3 queries.
<?php
//make database connect
mysql_connect("localhost", "asset_db", "asset_db") or die(mysql_error());
mysql_select_db("asset_db") or die(mysql_error());
//get type of asset
$type = mysql_query("
SELECT asset.type
From asset
WHERE asset.id = 93120
")
or die(mysql_error());
switch ($type){
case "Server":
//do some stuff that involves a mysql query
mysql_query("
SELECT asset.id
,asset.company
,asset.location
,asset.purchase_date
,asset.purchase_order
,asset.value
,asset.type
,asset.notes
,server.manufacturer
,server.model
,server.serial_number
,server.esc
,server.user
,server.prev_user
,server.warranty
FROM asset
LEFT JOIN server
ON server.id = asset.id
WHERE asset.id = 93120
");
break;
case "Laptop":
//do some stuff that involves a mysql query
mysql_query("
SELECT asset.id
,asset.company
,asset.location
,asset.purchase_date
,asset.purchase_order
,asset.value
,asset.type
,asset.notes
,laptop.manufacturer
,laptop.model
,laptop.serial_number
,laptop.esc
,laptop.user
,laptop.prev_user
,laptop.warranty
FROM asset
LEFT JOIN laptop
ON laptop.id = asset.id
WHERE asset.id = 93120
");
break;
case "Desktop":
//do some stuff that involves a mysql query
mysql_query("
SELECT asset.id
,asset.company
,asset.location
,asset.purchase_date
,asset.purchase_order
,asset.value
,asset.type
,asset.notes
,desktop.manufacturer
,desktop.model
,desktop.serial_number
,desktop.esc
,desktop.user
,desktop.prev_user
,desktop.warranty
FROM asset
LEFT JOIN desktop
ON desktop.id = asset.id
WHERE asset.id = 93120
");
break;
}
?>
So far I am able to get asset.type into $type. How would I go about getting the rest of the variables (laptop.model to $model, asset.notes to $notes and so on)?
Thank you.
