homepage Welcome to WebmasterWorld Guest from 54.234.0.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Strange Update syntax error
wrong data is getting inserted to db
Justo



 
Msg#: 4197077 posted 9:24 am on Sep 6, 2010 (gmt 0)

Hey,

im using a php script to get info from an excel doc and import it into a database.
the problem im having is that when i run the script it only inserts 1s into the database for eg
if i was trying to insert 45 into db it would put a 1

heres the code im using i my self cant find anything wrong


require_once '../Excel/reader.php';



$data = new Spreadsheet_Excel_Reader();

$data->read("export2010-09-06 (1).xls");

//error_reporting(E_ALL ^ E_NOTICE);
$coloumn = $_POST['table'];
$file = $_POST['file'];

mysql_connect("127.0.0.1","user","pass");
mysql_select_db("jedi");
for ($j = 1; $j <= $data->sheets[0]['numRows']; $j++)
{

$name = $data->sheets[0]['cells'][$j+1][1];
$score = $data->sheets[0]['cells'][$j+1][4];
$output = explode("/",$score);

$query = "UPDATE main SET `". $coloumn ."`=" . $output[0] . ", `" . $coloumn . "t`=" . $output[1] . " WHERE `name`='" . $name . "'";
echo $query."<br>";
mysql_query($query) or die(mysql_error());
echo $data->sheets[0]['cells'][$j+1][1]."<br>";


}
i have the script echo the name and the syntax its using what it outputs is the following

UPDATE main SET `ohs`=16, `ohst`=16 WHERE `name`='Bob'
Bob

but all im getting in ohs is 1 and in ohst im getting nothing

thanks in advance guys

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4197077 posted 4:05 pm on Sep 6, 2010 (gmt 0)

What are the data types of those columns?

Justo



 
Msg#: 4197077 posted 4:14 pm on Sep 6, 2010 (gmt 0)

they are set too varchar

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4197077 posted 4:54 pm on Sep 8, 2010 (gmt 0)

Then that's your problem. First, they really shouldn't be, if they are integers you want an integer field, it is faster and more appropriate. Then this would work.

UPDATE main SET `ohs`=16, `ohst`=16 WHERE `name`='Bob'

because you you don't need to quote integer fields. What I find surprising though is that this should give you a mysql syntax error and it doesn't, and is instead inserting a 1. Try quoting your fields, which is required on textual and date types.

UPDATE main SET `ohs`='16', `ohst`='16' WHERE `name`='Bob'

Justo



 
Msg#: 4197077 posted 12:56 pm on Sep 9, 2010 (gmt 0)

yer i tried setting them to int but it still does not work.

also tried quoting just gives sql error

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4197077 posted 5:53 pm on Sep 9, 2010 (gmt 0)

It shouldn't . . . . even though quoted aren't required on numeric fields it's still ok to quote them.


$query = "UPDATE main SET `". $coloumn ."`='" . $output[0] . "', `" . $coloumn . "t`='" . $output[1] . "' WHERE `name`='" . $name . "'";

or if these will always be integer fields,

$query = "alter table main change `" . $coloumn . "` `" $coloumn . "` int(11)";

$query = "alter table main change `" . $coloumn . "t` `" $coloumn . "t` int(11)";

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4197077 posted 6:18 pm on Sep 9, 2010 (gmt 0)

Maybe it's a PHP (or MySQL) thing, but unless your field names actually have backwards apostrophes in them, it seems like "UPDATE main SET `ohs`='16', `ohst`='16' WHERE `name`='Bob'" should throw an error. Methinks it should be "UPDATE main SET ohs='16', ohst='16' WHERE `name`='Bob'" (with no backwards apostrophes around the field names).

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4197077 posted 5:14 am on Sep 10, 2010 (gmt 0)

It is a mysql thing. The backticks are not usually necessary, but avoids any issues you have with naming a field that is a reserved word. Example, if you manage to name a column "date" or "curdate", those are a data types and an internal mysql function, respectively.

insert into table (`date`,`curdate`) values ('2010-01-01',curdate());

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved