Why can I query with an int but not a string here? PHP MySQL Datatypes

Posted by CT on Stack Overflow See other posts from Stack Overflow or by CT
Published on 2010-05-24T01:25:19Z Indexed on 2010/05/24 1:30 UTC
Read the original article Hit count: 325

Filed under:
|
|
|

I am working on an Asset Database problem. I receive $id from $_GET["id"]; I then query the database and display the results.

This works if my id is an integer like "93650" but if it has other characters like "wci1001", it displays this MySQL error:

Unknown column 'text' in 'where clause'

All fields in tables are of type: VARCHAR(50)

What would I need to do to be able to use this query to search by id that includes other characters?

Thank you.

<?php

<?php

/* 
*  ASSET DB FUNCTIONS SCRIPT
*
*/

# connect to database
function ConnectDB(){

    mysql_connect("localhost", "asset_db", "asset_db") or die(mysql_error());
    mysql_select_db("asset_db") or die(mysql_error());
}

# find asset type returns $type
function GetAssetType($id){

    $sql = "SELECT asset.type
    From asset
    WHERE asset.id = $id";
    $result = mysql_query($sql)
    or die(mysql_error());
    $row = mysql_fetch_assoc($result);
    $type = $row['type'];
    return $type;
}

# query server returns $result (sql query array)
function QueryServer($id){

    $sql = "
    SELECT asset.id
    ,asset.company
    ,asset.location
    ,asset.purchaseDate
    ,asset.purchaseOrder
    ,asset.value
    ,asset.type
    ,asset.notes
    ,server.manufacturer
    ,server.model
    ,server.serialNumber
    ,server.esc
    ,server.warranty
    ,server.user
    ,server.prevUser
    ,server.cpu
    ,server.memory
    ,server.hardDrive
    FROM asset
    LEFT JOIN server
        ON server.id = asset.id
    WHERE asset.id = $id
    ";
    $result = mysql_query($sql);
    return $result;
}



# get server data returns $serverArray
function GetServerData($result){

    while($row = mysql_fetch_assoc($result))
    {
        $id = $row['id'];
        $company = $row['company'];
        $location = $row['location'];
        $purchaseDate = $row['purchaseDate'];
        $purchaseOrder = $row['purchaseOrder'];
        $value = $row['value'];
        $type = $row['type'];
        $notes = $row['notes'];
        $manufacturer = $row['manufacturer'];
        $model = $row['model'];
        $serialNumber = $row['serialNumber'];
        $esc = $row['esc'];
        $warranty = $row['warranty'];
        $user = $row['user'];
        $prevUser = $row['prevUser'];
        $cpu = $row['cpu'];
        $memory = $row['memory'];
        $hardDrive = $row['hardDrive'];
        $serverArray = array($id, $company, $location, $purchaseDate, $purchaseOrder,
            $value, $type, $notes, $manufacturer, $model, $serialNumber, $esc, $warranty,
            $user, $prevUser, $cpu, $memory, $hardDrive);
    }
    return $serverArray;
}

# print server table
function PrintServerTable($serverArray){

    $id = $serverArray[0];
    $company = $serverArray[1];
    $location = $serverArray[2];
    $purchaseDate = $serverArray[3];
    $purchaseOrder = $serverArray[4];
    $value = $serverArray[5];
    $type = $serverArray[6];
    $notes = $serverArray[7];
    $manufacturer = $serverArray[8];
    $model = $serverArray[9];
    $serialNumber = $serverArray[10];
    $esc = $serverArray[11];
    $warranty = $serverArray[12];
    $user = $serverArray[13];
    $prevUser = $serverArray[14];
    $cpu = $serverArray[15];
    $memory = $serverArray[16];
    $hardDrive = $serverArray[17];

    echo "<table width=\"100%\" border=\"0\"><tr><td style=\"vertical-align:top\"><table width=\"100%\" border=\"0\"><tr><td colspan=\"2\"><h2>General Info</h2></td></tr><tr id=\"hightlight\"><td>Asset ID:</td><td>";
    echo $id;
    echo "</td></tr><tr><td>Company:</td><td>";
    echo $company;
    echo "</td></tr><tr id=\"hightlight\"><td>Location:</td><td>";
    echo $location;
    echo "</td></tr><tr><td>Purchase Date:</td><td>";
    echo $purchaseDate;
    echo "</td></tr><tr id=\"hightlight\"><td>Purchase Order #:</td><td>";
    echo $purchaseOrder;
    echo "</td></tr><tr><td>Value:</td><td>";
    echo $value;
    echo "</td></tr><tr id=\"hightlight\"><td>Type:</td><td>";
    echo $type;
    echo "</td></tr><tr><td>Notes:</td><td>";
    echo $notes;
    echo "</td></tr></table></td><td style=\"vertical-align:top\"><table width=\"100%\" border=\"0\"><tr><td colspan=\"2\"><h2>Server Info</h2></td></tr><tr id=\"hightlight\"><td>Manufacturer:</td><td>";
    echo $manufacturer;
    echo "</td></tr><tr><td>Model:</td><td>";
    echo $model;
    echo "</td></tr><tr id=\"hightlight\"><td>Serial Number:</td><td>";
    echo $serialNumber;
    echo "</td></tr><tr><td>ESC:</td><td>";
    echo $esc;
    echo "</td></tr><tr id=\"hightlight\"><td>Warranty:</td><td>";
    echo $warranty;
    echo "</td></tr><tr><td colspan=\"2\">&nbsp;</td></tr><tr><td colspan=\"2\"><h2>User Info</h2></td></tr><tr id=\"hightlight\"><td>User:</td><td>";
    echo $user;
    echo "</td></tr><tr><td>Previous User:</td><td>";
    echo $prevUser;
    echo "</td></tr></table></td><td style=\"vertical-align:top\"><table width=\"100%\" border=\"0\"><tr><td colspan=\"2\"><h2>Specs</h2></td></tr><tr id=\"hightlight\"><td>CPU:</td><td>";
    echo $cpu;
    echo "</td></tr><tr><td>Memory:</td><td>";
    echo $memory;
    echo "</td></tr><tr id=\"hightlight\"><td>Hard Drive:</td><td>";
    echo $hardDrive;
    echo "</td></tr><tr><td colspan=\"2\">&nbsp;</td></tr><tr><td colspan=\"2\">&nbsp;</td></tr><tr><td colspan=\"2\"><h2>Options</h2></td></tr><tr><td colspan=\"2\"><a href=\"#\">Edit Asset</a></td></tr><tr><td colspan=\"2\"><a href=\"#\">Delete Asset</a></td></tr></table></td></tr></table>";
}


?>

__

/* 
*  View Asset
*
*/

# include functions script
include "functions.php";

$id = $_GET["id"];
if (empty($id)):$id="000";
endif;
ConnectDB();
$type = GetAssetType($id);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="style.css" />
<title>Wagman IT Asset</title>
</head>

<body>
    <div id="page">
                <div id="header">
                  <img src="images/logo.png" />
                </div>

                </div>

                <div id="content">
                    <div id="container">

                        <div id="main">
                        <div id="menu">
                            <ul>
                                <table width="100%" border="0">
                                <tr>
                                <td width="15%"></td>
                                <td width="30%%"><li><a href="index.php">Search Assets</a></li></td>
                                <td width="30%"><li><a href="addAsset.php">Add Asset</a></li></td>
                                <td width="25%"></td>
                                </tr>
                                </table>
                          </ul>
                        </div>
                        <div id="text">
                        <ul>
                        <li>
                        <h1>View Asset</h1>
                        </li>
                        </ul>
                        <?php
                        if (empty($type)):echo "<ul><li><h2>Asset ID does not match any database entries.</h2></li></ul>";
                        else:
                        switch ($type){
                        case "Server":
                        $result = QueryServer($id);
                        $ServerArray = GetServerData($result);
                        PrintServerTable($ServerArray);
                        break;
                        case "Desktop";

                        break;
                        case "Laptop";

                        break;
                        }
                        endif;
                        ?>


                        </div>

                        </div>
                </div>
                <div class="clear"></div>
                <div id="footer" align="center">
                    <p>&nbsp;</p>
                </div>
                </div>
                <div id="tagline">
                Wagman Construction - Bridging Generations since 1902
                </div>


</body>
</html>

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql