Add field to query incorrect

PHP
1

hello
in the attached code, how do i add another field to the query? ie, i have all the code to create the result and would like to add further values such as $dept? what is the correct way to code query? i must stress that i am using php 4.4.7 so json_encode is out. the code is working but just need to find a way to add the $dept to the query? many thanks

$dept  = array();
$box = array();

while ($row = mysql_fetch_array($result)) {
          $dept[] = $row['department'];
          $box[] = $row['custref'];
}

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" );
header("Cache-Control: no-cache, must-revalidate" );
header("Pragma: no-cache" );
header("Content-type: application/json");
$json = "";
$json .= "{\
";
$json .= "dept: [\\"". implode('","', $dept). "\\"],\
";
$json .= "box: [\\"". implode('","', $box) ."\\"]\
";
$json .= "}\
";
echo $json;

$sql = "INSERT INTO `act` (`item`) VALUES ('". implode("'),('", $box) . "')";
$result = runSQL($sql);
2

guido. sorry, i do not understand your answer. where have i changed the table layout? how do i now get values of $dept and $box and insert them in db? many thanks

3

Of course, you changed the table layout without saying. :rolleyes:
You can’t use implode anymore. You’ll have to construct the values part of the query looping through the two arrays $dept and $box.

4

guido. here are results of the echo:

{
dept: [“BASW”,“BASW”],
box: [“BASW10455C”,“BASW10455D”]
}
INSERT INTO act (department, item) VALUES (‘BASW’),(‘BASW’),(‘BASW10455C’),(‘BASW10455D’)mysql error Column count doesn’t match value count at row 1 in query INSERT INTO act (department, item) VALUES (‘BSAW’),(‘BSAW’),(‘BSAW10455C’),(‘BSAW10455D’)

5

do the echo of $sql before you call the runSql function.
And you might want to change that ‘die’ message into something more helpful, for example:


$result = mysql_query($rsql) or die ('mysql error ' . mysql_error() . ' in query ' . $rsql);
6

hi guido

i did echo $sql; and it returned this:

{
dept: ["BASW","BASW"],
box: ["BASW10455C","BASW10455D"]
}
test

i think test must be reffering to my connection code:

function runSQL($rsql) {
	$hostname = "localhost";
	$username = "root";
	$password = "";
	$dbname   = "sample";
	$connect = mysql_connect($hostname,$username,$password) or die ("Error: could not connect to database");
	$db = mysql_select_db($dbname);
	$result = mysql_query($rsql) or die ('test'); 
	return $result;
	mysql_close($connect);
}

funny thing is that it works with the original code i posted just not with the addition. also, in firebug i got a json tab in the console when the query finishes but not now. thanks

7

Do an echo of $query and post the result here.
Is the query correct?

8

hi thanks for reply. unfortunately, it dosen’t work. it dosen’t return the values or enter into db.

$sql = "INSERT INTO `act` (`department`,`item`) VALUES ('". implode("'),('", $dept) . "'),('" . implode("'),('", $box) . "')";
$result = runSQL($sql);
9 

$sql = "INSERT INTO `act` (`item`) VALUES ('". implode("'),('", $box) . "'),('" . implode("'),('", $dept) . "')";