Reply to: Any PHP mysqli experts?

[Lance]

A metasyntactic variable (see https://en.wikipedia.org/wiki/Foobar)

(Or a corruption of fubar )

Foobar is similar to Bob and Alice in cryptography.

[DoctorStrangelove]

PS Not quite sure what atomic is, have to have a Google.

You called? Supplies of Cobalt Thorium G are quite healthy at the moment.

[xoggoth]

Thanks all but no need for any more solutions, it's just a little local history website, form will only be used by me and maybe 2 other people. Working ok at mo.

I do have a much more complicated small businesss website with checkout, one of the lines here, (daddress = sanitised version of address etc.) works fine:

$sql = $con->query("UPDATE shop SET name='$dname',school='$dschool',address='$daddress ',postcode='$postcode',country='$country',
tel='$tel',email='$email',comment='$dcomment' WHERE custid = '$custid'");
if ($sql === FALSE)
{
echo "Error: " . $sql . "<br>" . $con->error;
echo "Update cust 1 failed";
}

Just that that aproach with multiple items did not work in the little history thing, dunno why.

[Paralytic]

You said your solution is working? Are you sure it is? ie. is it actually inserting the values of variables $date, $title etc, or is it inserting the string values "$date", "$title" etc.

Here's what i think it should be (typed from memory, not tested). The {} inserts the variables into the query string.

$sql = $con->query("UPDATE whlist SET date='{$date}', title='{$title}', speaker='{$speaker}', description='{$description}'
WHERE id={$id}"); 

The above assumes id is numeric key, otherwise

$sql = $con->query("UPDATE whlist SET date='{$date}', title='{$title}', speaker='{$speaker}', description='{$description}'
WHERE id='{$id}'"); 

If you're still getting errors, do

print_r($sql); 

To show you what the raw SQL being executed is.

[Snooky]

That's a pretty flaky approach, inefficient and no transactional integrity, what happens if the date update works but title doesn't? Do it as a single atomic UPDATE.

Agreed, this makes any db dev want to weep

[xoggoth]

Cheers TGB. I do sanitise, remove quotes etc, and check if update worked but didn't put those lines here to keep it simple. As per post I would normally do a single update line but not working here for some reason, my flaky aproach only one that worked. Not a site of any importance anyway.

PS Not quite sure what atomic is, have to have a Google.
PPS Nice cartoon. xkcd: Exploits of a Mom

[TheGreenBastard]

Ah! I didn't see the relevance of your comment initially but you've sorted it for me, I just updated each item individually and it worked.

$sql = $con->query("UPDATE whlist SET date='$date' WHERE id=$id");
$sql = $con->query("UPDATE whlist SET title='$title' WHERE id=$id");
$sql = $con->query("UPDATE whlist SET speaker='$speaker' WHERE id=$id");
$sql = $con->query("UPDATE whlist SET description='$description' WHERE id=$id");

$con->error still showing same error message but I'll just comment that out.

Thanks woody1

Can't understand why I never had this problem before in my business website, got lots of far more complicated update lines. Maybe some property of the table?

That's a pretty flaky approach, inefficient and no transactional integrity, what happens if the date update works but title doesn't? Do it as a single atomic UPDATE.

Also how are the $vars sanitized? What if $id became "1; DELETE FROM whlist"?

[HoofHearted]

Where does foobar come from?

A metasyntactic variable (see https://en.wikipedia.org/wiki/Foobar)

(Or a corruption of fubar )

[xoggoth]

Yeh, didn't use foobar. Where does foobar come from? And that ipse lorum ... or something like that you always see in examples. I always use rude words in my test data, much easier. Anyway, thanks again.

[woody1]

Thanks woody1

No problem, although I was just groping in the dark.

Anyway, ignore my previous post.

[woody1]

Cheers. That does update row 1 but still get error messages.

Presumably you changed foobar for an actual column? Strange that it now updates a row but still throws errors, although presumably not a syntax error.

As a pure guess, the syntax error might have been something to do with the $var and/or single quotes.

[xoggoth]

Ah! I didn't see the relevance of your comment initially but you've sorted it for me, I just updated each item individually and it worked.

$sql = $con->query("UPDATE whlist SET date='$date' WHERE id=$id");
$sql = $con->query("UPDATE whlist SET title='$title' WHERE id=$id");
$sql = $con->query("UPDATE whlist SET speaker='$speaker' WHERE id=$id");
$sql = $con->query("UPDATE whlist SET description='$description' WHERE id=$id");

$con->error still showing same error message but I'll just comment that out.

Thanks woody1

Can't understand why I never had this problem before in my business website, got lots of far more complicated update lines. Maybe some property of the table?

[alanc]

What errors do you get ?

[xoggoth]

Cheers. That does update row 1 but still get error messages.

[alanc]

I don't think you are concatenating your strings and variables correctly. Try


$sql = $con->query("UPDATE whlist SET date='" . $date . "' WHERE id='" . $id . "'");