mysql [PHP] Mysql query not Updating, but query works in PHPMyAdmin

[whitebread]

This is my PHP code:

$Category1 = $_POST['textboxCategory1'];

$Category2 = $_POST['textboxCategory2'];

$Category3 = $_POST['textboxCategory3'];

$Category4 = $_POST['textboxCategory4'];

$Category5 = $_POST['textboxCategory5'];

$con = mysql_connect("intralook.ca","myusername","mypassword");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("intralo1_shop", $con);

$qry = "UPDATE Categories SET CategoryName = '" . $Category1 . "' WHERE CatID=1; " . "UPDATE Categories SET CategoryName = '" . $Category2 . "' WHERE CatID=2; " . "UPDATE Categories SET CategoryName = '" . $Category3 . "' WHERE CatID=3; " . "UPDATE Categories SET CategoryName = '" . $Category4 . "' WHERE CatID=4; " . "UPDATE Categories SET CategoryName = '" . $Category5 . "' WHERE CatID=5; ";

echo $qry;

mysql_query($qry);

mysql_close($con);

The query it outputs is:

UPDATE Categories SET CategoryName = 'Item 1' WHERE CatID=1; UPDATE Categories SET CategoryName = 'Item 2' WHERE CatID=2; UPDATE Categories SET CategoryName = 'Item 3' WHERE CatID=3; UPDATE Categories SET CategoryName = 'Item 4' WHERE CatID=4; UPDATE Categories SET CategoryName = 'Item 5' WHERE CatID=5;

When I run that in PHPMyAdmin, it works properly. I do not get any errors when I try to run the PHP code by submitting the form. It shows a blank page (except for the query, which I put an echo statement in to show for debugging).

[DomZ]

Obvious question, but have you tried running the query you output via echo with phpmyadmin? I can almost guarantee it will have an error

Also try:

echo mysql_error();

[Phouchg]

Between the query and closing the link:

echo 'MySQL barked: '.mysql_errno($link).': '.mysql_error($link)."\n";
[/CODE]

[size=2]Edit: [b]DomZ[/b] ninja'd me. Congrats.[/size]

[Jose_49]

Nope. I know what's the problem. I've ran to it as well.

Are you using a paid hosting? If so, then it means that the user you created does not have the privileges of Updating. Modify the user at your account. It took me a whole day to figure that out.

Hope that this helps out.

[-Alex-]

Nope, that's not the problem here. The problem is thus:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Try this (rewritten using MySQL improved, made a bit more secure, and tidied up a bit):

$dh = mysqli_connect("intralook.ca", "myusername", "mypassword", "intralo1_shop");
if (!$dh) die('Could not connect: ' . mysqli_error());

$post = array_map(array($dh, 'real_escape_string'), $_POST);
foreach($post as $key => $value) ${$key} = $value;

mysqli_multi_query($dh,
  "UPDATE `Categories` SET `CategoryName` = '$textboxCategory1' WHERE `CatID`=1;" .
  "UPDATE `Categories` SET `CategoryName` = '$textboxCategory2' WHERE `CatID`=2;" .
  "UPDATE `Categories` SET `CategoryName` = '$textboxCategory3' WHERE `CatID`=3;" .
  "UPDATE `Categories` SET `CategoryName` = '$textboxCategory4' WHERE `CatID`=4;" .
  "UPDATE `Categories` SET `CategoryName` = '$textboxCategory5' WHERE `CatID`=5;"
);

mysqli_close($dh);

[Xoligy]

IM just basic php but pretty sure how your writing teh code is wrong, also your pointing to mysqlquery when you need mysqlmultiquery: http://php.net/manua...multi-query.php

I think this is the way i was shown on a forum a while back when i wanted to do the same as you, its on the link above:

<?php
// WORKING CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!
?>

//then again it might of been this way

<?php
$link = mysqli_connect('localhost', 'user', 'pw', 'db');

$script = "INSERT INTO T1('pk', 'val') VALUES('1', 'A');";
$script .= "INSERT INTO T1('pk', 'val') VALUES('2', 'B');"
$script .= "INSERT INTO T1('pk', 'val') VALUES('3', 'C');"
$script .= "ALTER TABLE T1 ADD COLUMN `foo` INT(1) NOT NULL;";
$script .= "UPDATE T1 SET val = 'D' where pkkkkkkkkkkkk = '1'"; // Note that this statement will force a SQL Error since column pkkkkkkkkkkkk doesn't exist

mysqli_autocommit($link, false); // set autocommit to false

mysqli_multi_query($link, $script); // execute statements

mysqli_rollback($link); // rollback all statements
?>

Someone way better at php will definitely corrext me though lol

[-Alex-]

The code I posted above will work. The link you posted to the manual is mysqli_multi_query, so it will not work by directly changing mysql_query to mysqli_multi_query as the whole block of code would need to be rewritten in MySQLi (as in the post I made) :)

[Xoligy]

Aaah ok like i said im basic i need to relearn what i nkew been a few years but i had something similar to what i posted given to me too lazy to look for the exact code thanks for correcting me though =)

[Laser]

I would just move your echo and see what happens.

 
echo $qry;

mysql_query($qry);

mysql_close($con);
[/CODE]

[CODE]
mysql_query($qry);

echo $qry;

mysql_close($con);
[/CODE]

[-Alex-]

That would make zero difference. The solution is mysqli_multi_query() or a set of mysql_query() commands, each containing only 1 query.