How to get PHP variables from this MySQL query?
I am working on an asset database problem using PHP / MySQL.
In this script, I would like to search for my assets by resource id and return all related fields.
First, I query the database assets table and find the asset type. Then, depending on the type, I run 1 of 3 requests.
<?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 can get object.type in $ type. How can I get the remaining variables (laptop.model to $ model, asset.notes to $ notes, etc.)?
Thanks.
a source to share
$sql = "YOUR QUERY";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res))
{
echo 'Start Record<br />';
echo $row['type'].'<br />';
echo $row['company'].'<br />';
echo $row['location'].'<br />';
echo 'End Record<br /> <br />';
}
Try to see what you get, then you can use the data as you see fit.
You can also look at mysql_fetch_array
, mysql_fetch_row
or mysql_fetch_object
. Choose the one that best suits your needs.
a source to share
What you are doing won't work:
$type = mysql_query("
SELECT asset.type
From asset
WHERE asset.id = 93120
")
This puts the result set to type $, not the type itself.
After that you need to get the string, then enter the field:
$result = mysql_query("
SELECT asset.type
From asset
WHERE asset.id = 93120
")
if($row = mysql_fetch_object($result)){
$type = $row->type;
// NOW you have the type in $type. Do something similar with the rest of the queries.
}
a source to share
I think you would like to do something like this:
$result = mysql_query($query);
$i = array();
while ($data = mysql_fetch_assoc($result)) {
$i[] = $data;
}
This would make $ i a multidimensional array containing all your request data and you could use it by doing the following
foreach ($i as $key => $value) {
echo $value['model'];
echo $value['serial'];
etc......
}
a source to share
You can take a look at http://php.net/manual/en/function.mysql-query.php .
A mysql_query
returns a resource that contains the results of your query. This resource can be read like this:
$sql_result = mysql_query( [here your stuff] );
while ($row = mysql_fetch_assoc($sql_result)){
echo $row['model']; // prints the model
}
So, you need to go through the result and go through row
. row
then is an associative array containing single fields.
Hope it helps.
a source to share
it should only be one table computers
so your code will just be:
<?php
//make database connect
mysql_connect("localhost", "asset_db", "asset_db") or die(mysql_error());
mysql_select_db("asset_db") or die(mysql_error());
$sql = "SELECT a.id, a.company, a.location, a.purchase_date, a.purchase_order,
a.value, a.type, a.notes, c.manufacturer, c.model,
c.serial_number, c.esc, c.user, c.prev_user, c.warranty
FROM asset a
LEFT JOIN computers c
ON c.id = a.id
WHERE a.id = 93120";
$res = mysql_query($sql) or trigger_error(mysql_error().$sql);
$data=array();
while($row = mysql_fetch_assoc($res)) $data[] = $row;
// now $data array contains all the rows returned
?>
Every time you see a double code, you know that you are doing something wrong.
a source to share