|
|
Posted by Adam Plocher on 07/07/06 19:00
oh AND, your update should be formatted like this
UPDATE table SET col1='value1', col2='value2' WHERE col1=$id
NOT
UPDATE table SET (col1, col2) VALUES ('value1', 'value2') WHERE
col1=$id
The "values" keyword is for INSERTs, not UPDATEs
Adam Plocher wrote:
> It looks like you don't have a space between the closing parans ) and
> WHERE.
>
> Also, are you secure from sql injection attacks? Be sure to escape bad
> characters and check for is_numeric (if the value is supposed to be
> numeric) before putting any user-defined data into a query. It is far
> too easy for people to run a DELETE or DROP statement against your
> database when you're not secure against injections.
>
> More injection info @
> http://www.digitalpropulsion.org/Programming/SQL_Injections_in_PHP_with_MySQL
>
> sloane.irwin@gmail.com wrote:
> > Hi,
> >
> > I've created a web based database that can enter, view, and delete
> > information from, but I'm having trouble with the Update form. I put in
> > the echo mysql error and it says I have a syntax error, but I can't
> > tell what I'm doing wrong, the query looks okay to me...
> >
> > Here's the code for the form:
> > <html>
> > <head>
> > <title>New Computer Database Entry</title>
> > <link href="2col_leftNav.css" rel="stylesheet" type="text/css">
> > <style type="text/css">
> > <!--
> > .style1 {color: #334d55}
> > .style2 {
> > font-size: 100%;
> > color: #334d55;
> > }
> > -->
> > </style>
> > </head>
> > <?
> > require('db_login.php');
> > require('db_connect.php');
> > ?>
> > <body>
> >
> > <?
> > $db = mysql_connect($host, $user, $pass);
> > mysql_select_db($db_name,$db);
> > $id=$_GET["id"];
> > $result = mysql_query("SELECT * FROM data WHERE id=$id",$db);
> >
> >
> > $myrow = mysql_fetch_assoc($result);
> >
> >
> >
> > ?>
> > <p align="center"><img src="osu-hort-logo-color.gif" width="469"
> > height="173" /></a></p>
> > <form method="get" action="db_change.php?id=<? $id ?>">
> > <label><span class="Boldtitle">Initial Information:</span><span
> > class="story"><br>
> > <br>
> > <br>
> > Inventory#
> > <input type="text" name="inventory" value="<? echo
> > $myrow["inventory"]?>" >
> > </span></label>
> >
> >
> > <span class="story">
> > <label>Service Tag/Serial #
> > <input type="text" name="serial" value="<? echo $myrow["serial"]?>">
> > </label>
> > </span>
> > <p class="style1">
> >
> > <span class="story">
> > <label>First Name
> > <input type="text" name="first" value="<? echo $myrow["first"]?>">
> > </label>
> >
> > <label>Last Name
> > <input type="text" name="last" value="<? echo $myrow["last"]?>">
> > </label>
> > </span></p>
> > <p class="story"><span class="style1"><strong>Primary User
> > Name</strong></span>
> > <input type="text" name="pri_user" value="<? echo
> > $myrow["pri_user"]?>">
> > <span class="style1"><strong>Primary User E-Mail </strong></span>
> > <input type="text" name="email" value="<? echo $myrow["email"]?>">
> > </p>
> > <p class="story"><span class="story">
> > <label>Purchase Date
> > <input type="text" name="buy_date" value="<? echo
> > $myrow["buy_date"]?>">
> > </label>
> >
> > <span class="style1"><strong>Warranty Expiration</strong></span>
> > <label>
> > <input type="text" name="warranty" value="<? echo
> > $myrow["warranty"]?>">
> > </label>
> > </span></p>
> > <input type='hidden' name='id' value='<? echo $myrow["id"]?>'>
> > <p class="story style1">
> >
> > <span class="story">
> > <label>Vendor
> > <input type="text" name="vendor" value="<? echo
> > $myrow["vendor"]?>">
> > </label>
> > <label>Computer Name
> > <input type="text" name="computer_name" value="<? echo
> > $myrow["computer_name"]?>">
> > </label>
> > </span></p>
> > <p class="story style1"><span class="story">
> > <label>Date Entered
> > <input type="text" name="date_entered" value="<? echo
> > $myrow["date_entered"]?>">
> > </label>
> > </span></p>
> > <strong class="Boldtitle style2">Location:</strong>
> > </p>
> > </p>
> > <p class="style1">
> >
> >
> > <span class="story">
> > <label>City
> > <input type="text" name="city" value="<? echo $myrow["city"]?>">
> > </label>
> >
> > <label>Location Building
> > <select name="building" value="<? echo $myrow["building"]?>">
> > <option>Farm</option>
> > <option>Gourley</option>
> > <option>Gourley Greenhouse</option>
> > <option>Home</option>
> > <option>Howlett</option>
> > <option>Howlett Greenhouse</option>
> > <option>Kottman</option>
> > <option>Turf Center</option>
> > <option>Williams</option>
> > <option>Williams Greenhouse</option>
> > <option selected>OPGC (BIO CONTAINMENT)</option>
> > <option>Other</option>
> > </select>
> > </label>
> > </span></p>
> > <p class="style1">
> >
> >
> > <span class="story">
> > <label> </label>
> >
> > <label>Room Number</label>
> >
> > <label>
> > <input name="room" type="text" value="<? echo
> > $myrow["room"]?>"size="6" maxlength="4">
> > <br>
> > <br>
> > <br>
> > <br>
> > </label>
> > </span>
> > <label><span class="Boldtitle">Computer Information:</span></label>
> > <span class="story">
> > <label><br>
> > <br>
> > </label>
> >
> > <label>Brand</label>
> >
> > <label>
> > <input name="brand" type="text" value="<? echo $myrow["brand"]?>">
> > </label>
> >
> > <label>Model
> > <input type="text" name="model" value="<? echo $myrow["model"]?>">
> > </label>
> > </span></p>
> > <p class="style1">
> >
> >
> > <span class="story">
> > <label></label>
> > <label>OS
> > <input name="os" type="text" size="10" maxlength="10" value="<?
> > echo $myrow["os"]?>">
> > </label>
> >
> > <label>Processor Speed (GHz)
> > <input name="processor" type="text" size="8" maxlength="8"
> > value="<? echo $myrow["processor"]?>">
> > </label>
> > </span>
> > <p class="style1">
> >
> >
> > <span class="story">
> > <label></label>
> >
> > <label>Memory Size (Mb)
> > <input name="memory" type="text" size="8" maxlength="8" value="<?
> > echo $myrow["memory"]?>">
> > </label>
> >
> > <label>HD Size (Gb)
> > <input name="hd" type="text" size="8" maxlength="8" value="<? echo
> > $myrow["hd"]?>">
> > </label>
> > </span><span class="story">
> >
> >
> >
> > </span> </p>
> > <p class="style1"><span class="story"><strong>Removable Media
> > Drives</strong></span> </p>
> > <p class="style1"><span class="story"><strong>
> > <label>1</label>
> > <strong>
> > <input name="drive1" type="text" size="40" maxlength="8" value="<?
> > echo $myrow["drive1"]?>">
> > </strong>
> > </label>
> > </span></p>
> > <p class="style1">
> > <label><span class="story">2</span></label>
> >
> > <input name="drive2" type="text" size="40" maxlength="8" value="<?
> > echo $myrow["drive2"]?>">
> >
> > </label>
> > </span></span></p>
> > <p class="style1">
> > <span class="story">
> > <label>3</label>
> > <input name="drive3" type="text" size="40" maxlength="8" value="<?
> > echo $myrow["drive3"]?>">
> > </span></p>
> > <p class="style1">
> > <span class="story">
> > <label>4</label>
> > </span><strong>
> > <input name="drive4" type="text" size="40" maxlength="8" value="<?
> > echo $myrow["drive4"]?>">
> > </strong></p>
> > <p class="style1"> </p>
> > <p class="story">.</p>
> > <p class="story"><strong>Additional Information: </strong></p>
> > <p class="style1">
> > <label>
> > <textarea name="comments" cols="65" rows="8" value="<? echo
> > $myrow["comments"]?>"></textarea>
> > </label>
> > <BR>
> > </p>
> > <p class="style1"> <br />
> > <input type="submit" value="Submit">
> > </p>
> > </form>
> >
> > </body>
> > </html>
> >
> >
> > </body>
> > </html>
> >
> >
> >
> >
> >
> >
> > And the code for the php file that it references to update the DB:
> >
> > <html>
> > <body>
> > <?
> > require('db_login.php');
> > require('db_connect.php');
> >
> > //bring in id variable
> > $id=$_GET["id"];
> > // the table you want to add to
> > $table = 'data';
> >
> >
> > // begin the query
> >
> > $query = "UPDATE $table SET(";
> >
> >
> > // add fieldnames to insert
> >
> > $query .= 'inventory' . ',';
> > $query .= 'date_entered' . ',';
> > $query .= 'first' . ',';
> > $query .= 'last' . ',';
> > $query .= 'serial' . ',';
> > $query .= 'city' . ',';
> > $query .= 'brand' . ',';
> > $query .= 'model' . ',';
> > $query .= 'email' . ',';
> > $query .= 'building' . ',';
> > $query .= 'room' . ',';
> > $query .= 'computer_name' . ',';
> > $query .= 'pri_user' . ',';
> > $query .= 'warranty' . ',';
> > $query .= 'vendor' . ',';
> > $query .= 'os' . ',';
> > $query .= 'processor' . ',';
> > $query .= 'memory' . ',';
> > $query .= 'hd' . ',';
> > $query .= 'drive1' . ',';
> > $query .= 'drive2' . ',';
> > $query .= 'drive3' . ',';
> > $query .= 'drive4' . ',';
> > $query .= 'comments' ;
> >
> >
> >
> > // end fieldnames and begin values
> >
> > $query = $query . ') VALUES(';
> >
> >
> > // get values from $_REQUEST
> >
> > $query = $query . '"' . $_REQUEST['inventory'] . '", ';
> > $query = $query . '"' . $_REQUEST['date_entered'] . '", ';
> > $query = $query . '"' . $_REQUEST['first'] . '", ';
> > $query = $query . '"' . $_REQUEST['last'] . '", ';
> > $query = $query . '"' . $_REQUEST['serial'] . '", ';
> > $query = $query . '"' . $_REQUEST['city'] . '", ';
> > $query = $query . '"' . $_REQUEST['brand'] . '", ';
> > $query = $query . '"' . $_REQUEST['model'] . '", ';
> > $query = $query . '"' . $_REQUEST['email'] . '", ';
> > $query = $query . '"' . $_REQUEST['building'] . '", ';
> > $query = $query . '"' . $_REQUEST['room'] . '", ';
> > $query = $query . '"' . $_REQUEST['computer_name'] . '", ';
> > $query = $query . '"' . $_REQUEST['pri_user'] . '", ';
> > $query = $query . '"' . $_REQUEST['warranty'] . '", ';
> > $query = $query . '"' . $_REQUEST['vendor'] . '", ';
> > $query = $query . '"' . $_REQUEST['os'] . '", ';
> > $query = $query . '"' . $_REQUEST['processor'] . '", ';
> > $query = $query . '"' . $_REQUEST['memory'] . '", ';
> > $query = $query . '"' . $_REQUEST['hd'] . '", ';
> > $query = $query . '"' . $_REQUEST['drive1'] . '", ';
> > $query = $query . '"' . $_REQUEST['drive2'] . '", ';
> > $query = $query . '"' . $_REQUEST['drive3'] . '", ';
> > $query = $query . '"' . $_REQUEST['drive4'] . '", ';
> > $query = $query . '"' . $_REQUEST['comments'] . '"';
> >
> >
> > // end query
> >
> > $query = $query . ' )WHERE id=$id ';
> >
> >
> > // run INSERT query
> >
> > mysql_query($query, $db);
> >
> > echo mysql_error();
> > include('db_close.php');
> >
> >
> > echo $query;
> > ?>
> >
> > <script language="javascript">
> > <!--
> >
> > location.href = 'display2.php';
> >
> > -->
> > </script>
> >
> > </body>
> > </html>
[Back to original message]
|