开发者

How do I get PHP variables from this MySQL query?

开发者 https://www.devze.com 2022-12-31 17:00 出处:网络
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.

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 so开发者_JAVA百科me 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.


$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 that out to see what you get then you can use data as you wish;

You may also want to look at mysql_fetch_array, mysql_fetch_row or mysql_fetch_object. Choose which best suits your needs.


What you're doing will not work:

$type = mysql_query("
SELECT asset.type
From asset
WHERE asset.id = 93120
")

This puts a resultset into $type, not the type itself.

After you do that, you need to fetch a row, then fetch 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.
}


I guess you would want 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 of your query data and you could use it by doing the following

foreach ($i as $key => $value) {
  echo $value['model'];
  echo $value['serial'];
  etc......
}


Please see:

http://us2.php.net/manual/en/mysql.examples-basic.php

http://us2.php.net/manual/en/function.mysql-query.php


You may take a look at http://php.net/manual/en/function.mysql-query.php.

A mysql_query returns a resource which contains the results of your query. This resource can be read out the following way:

$sql_result = mysql_query( [here your stuff] );
while ($row = mysql_fetch_assoc($sql_result)){
  echo $row['model']; // prints the model
}

So, you have to loop through the result and traverse each row. row then is an associative array containing the single fields.

Hope this helps.


it must be just one table computers

so your code would be just:

<?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 doubled code, y know you're doing something wrong.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号