Hi,
$query = 'UPDATE allbooks SET catalog="$cat"';
Can someone tell me what’s wrong with this UPDATE statement. It does not update the column.
$cat contains valid data. The db is connected ok. I’ve tried all combinations of single/double quotes, brackets, (ok I’m inexperienced with mySQL so am playing), but it doesn’t want to do it.
Help please and thanks.
Mike
Not solved yet but if and when it’s solved I’ll post the solution. Now see my new post about missing rows!!
books is a small test table while allbooks contains more information and more rows. For the category question they are both suitable targets.
What’s the difference between the books table and the allbooks table?
Hi,
Thanks. I tried this literally and got an error. What’s the id? Should I be replacing id with a value? Help!
"UPDATE books SET catalog='$cat' WHERE id=" . $row['id'];
Can you give us the table structure of your table ‘books’?
<?php
include('misc.inc');
$connection = mysql_connect($host, $user, $password);
mysql_select_db($database, $connection);
$categories = array(
'Kunst',
'Kinderbüchen',
'Literatur',
'Geography',
'Photography'
);
while(true){
mysql_query(
sprintf(
"UPDATE books SET catalog = '%s' WHERE catalog = '' LIMIT 1",
$categories[rand(0, 4)]
),
$connection
);
if(0 === mysql_affected_rows($connection)){
break;
}
}
?>
Updates every record with an empty catalog column with a random category.
I am not sure about the structure of books table but I assume you have id field in it and you can try something like this:
include('misc.inc');
$connection = mysql_connect($host,$user,$password) or die ('No connection');
$db = mysql_select_db($database,$connection) or die ('No selection');
$query = "SELECT * FROM books";
$result = mysql_query($query) or die('mysql error ' . mysql_error() . ' in query ' . $query);
$ct1 = array('Kunst','Kinderbüchen','Literatur','Geography','Photography');
$ix = -1;
while ($row = mysql_fetch_array($result)){
extract($row);
$ix++;
$cat = $ct1[$ix];
echo $cat,"<br>",$ix,"<br>";
$query = "UPDATE books SET catalog='$cat' WHERE id=" . $row['id'];
mysql_query($query) or die('mysql error ' . mysql_error() . ' in query ' . $query);
echo $catalog, "<br>", $ix, "<br>";
if ($ix == 4) $ix = -1;
}
exit();
Note the update statement:
$query = "UPDATE books SET catalog='$cat' WHERE id=" . $row['id'];
And I am confused from where the $catalog variable comes in this line after update:
echo $catalog, "<br>", $ix, "<br>";
Hi to both who suggested
$query = "UPDATE books SET catalog='" . $cat . "'";
It worked but the update wasn’t correct.
BTW what is the meaning of this update statement since this statement in the loop will update your whole rows/records in the table ‘books’ with the last value of your array.
Explain, without code, what you’re trying to do with your data…
I have 5 different ‘catalogs’ in the array the purpose being to give each book in the books table a specific catalog. Then I can test another program which selects the books by catalog. What I’m trying to do is quite simple.
The echos prove that the fields contain what they should contain, the array is processed from [0] through [4] as a loop until end of table. The update works inasmuch that it puts one of the array values in the catalog field but it’s the same value in all rows! Almost as if it updates the first one (with the wrong array value) then holds on to that for all other updates. I hope it’s clear.
I have tested all variations on two different servers and locally all to no avail.
Mike
$query = "UPDATE books SET catalog='$cat'";
That should work since PHP evaluates variables in strings delimited by “” before processing the result. However, a consistent approach would be …
$query = "UPDATE books SET catalog='".$cat."'";
… whereby it doesn’t matter what is the structure of the PHP variable (plain, array, etc.) as proper string concatenation is followed. 
SNAP! 
The string interpolation is quite fine and quite valid with the following :
$query = "UPDATE books SET catalog='$cat'";
But would be separate the variable from whole string :
$query = "UPDATE books SET catalog='" . $cat . "'";
BTW what is the meaning of this update statement since this statement in the loop will update your whole rows/records in the table ‘books’ with the last value of your array.
Otherwise the code seems fine and should work to update all the rows/records in the table.
Let’s start again shall we? 
Explain, without code, what you’re trying to do with your data…
I’ d like to get all rows which…then with those…which…and finally…
i think it’s clear that you are having trouble getting your php code to work properly, and this isn’t really a mysql problem
Good idea.
I tried all sorts of combinations locally (XAMPP) and on my provider. Results below. The echo statements show me that the rest is functioning as it should.
There’s a $25 dollar reward to the one who solves the mystery.
Mike
The complete coding.
<?php
include('misc.inc');
$connection = mysql_connect($host,$user,$password)
or die ('No connection');
$db = mysql_select_db($database,$connection)
or die ('No selection');
$query = "SELECT * FROM books";
$result = mysql_query($query)
or die('mysql error ' . mysql_error() . ' in query ' . $query);
$ct1 = array('Kunst','Kinderbüchen','Literatur','Geography','Photography');
$ix = -1;
while ($row = mysql_fetch_array($result))
{
extract($row);
$ix++;
$cat = $ct1[$ix];
echo $cat,"<br>",$ix,"<br>";
$query = "UPDATE books SET catalog='$cat'";
$result2 = mysql_query($query)
or die('mysql error ' . mysql_error() . ' in query ' . $query);
echo $catalog,"<br>",$ix,"<br>";
if ($ix == 4)
$ix = -1;
};
exit;
?>
The results.
LOCAL & SERVER - BOTH PUT the word cat IN ALL ROWS = it ‘worked’!
$query = 'UPDATE books SET catalog="cat"';
$query = "UPDATE books SET catalog='cat'";
LOCAL & SERVER - PUTS ‘$cat’ IN ALL ROWS = not the value of $cat
$query = 'UPDATE books SET catalog="$cat"';
LOCAL - PUTS NOTHING IN ALL ROWS = no changes
SERVER - PUTS the value of $ct1[3] IN ALL ROWS = not the value of $ct1[$ix]
$query = "UPDATE books SET catalog='$cat'";
LOCAL & SERVER - PUTS ‘$ct1[$ix]’ IN ALL ROWS = not the value of $ct1[$ix]
$query = 'UPDATE books SET catalog="$ct1[$ix]"';
LOCAL - PUTS the value of $ct1[1] IN ALL ROWS = not the value of $ct1[$ix]
SERVER - PUTS NOTHING IN ALL ROWS = no changes
$query = "UPDATE books SET catalog='$ct1[$ix]'";
See attachments please.
I emptied all category fields and ran the code locally. It put $categories[1] - Kinderbüchen - in the category field in all rows. On the two servers it didn’t update any rows. Thanks anyway
Mike
maybe you could run the query yourself, but make sure you run it outside of php, say in the mysql workbench tool or phpmyadmin or heidisql or even the command line…
then you’ll see whether it’s the query or your php code that’s failing to update the table
PS Maybe someone could run this in their own environment and see what happens. Any clue could help.
Mike
nothing
perhaps your php code isn’t executing it
$query = "UPDATE allbooks SET catalog='$cat'";