CODEWITHSUNDEEP
phpmysql
Matt
Matt

Reputation: 278

SQL/PHP - REPLACE statement not working as intended

I'm trying to 'remove' part of a string in a DB entry after a delete command is made.

Say a PDF file gets deleted, i want to remove the reference of it by replacing it with '' (but keep the other references). That works fine, but now i also want to do it with another column, and the exact same setup doesn't seem to work and i can't figure out why.

This works:

$stmt_2 = $db->prepare("UPDATE data_videothek SET pdfAttachment = REPLACE(pdfAttachment, $id, '') WHERE pdfAttachment LIKE ?");
$stmt_2->execute(array( $like_id_string ));

This doesn't:

$stmt_2 = $db->prepare("UPDATE data_videothek SET speaker_img = REPLACE(speaker_img, $thefile, '' ) WHERE speaker_img LIKE ?");
$stmt_2->execute(array(  $like_filename_string ));

Both columns are defined as 'varchar' and all input in the query is as 'string'

I can't wrap my head around why the pdf one works, but the speaker_img doesn't.

The problem must be in REPLACE(speaker_img, $thefile, '' ) because if i replace that with a simple = '', the query performs fine and clears the whole cell if a match is made.

This is how i define the 2 input variables:

$thefile = (string)basename($row['filename'], ".png");
$like_filename_string = "%".$thefile."%";

For the curious why i save the info like that: There's only 1 field for the filenames, seperated by a "-", which when loaded gets 'exploded' into an array. It allows me to haven an open-ended number of attachments. Maybe not the best way of doing things?

Upvotes: 1

Views: 264

Answers (1)

Niloct
Niloct

Reputation: 9995

If you use parameters for $id and $thefile instead of variable interpolation, you won't need to worry about escaping chars, which is probably the reason the sql is malformed when you try to just use the value of $thefile inside a sql string.

Upvotes: 1

Related Questions