[SOLVED] oops! Trouble with updating my db.

[defeated]

I need to change the format of the content of one of my columns across all the rows in the table.

I can't get it to work. Here is the code.

$query=mysql_query("SELECT * FROM livejobs") or die(mysql_error());
while($result=mysql_fetch_array($query)){
$jobref=$result['jobref'];
$jobtitle=$result['jobtitle'];
$email=$result['email'];
$initialdescription=$result['description'] ;
$bad=array("<p> </p>","<strong>","</strong>","<div>","</div>","?");
$good=array("<br />","<span>","</span>","","","·");

$description = str_replace($bad,$good,$initialdescription);
$description=preg_replace("/<span[^>]+>/", "<span style='font-weight:bold;'>",$description);
$description=preg_replace("/<p[^>]+>/","<p style='text-align:center;'>",$description);
$description=preg_replace("/<a href=[^>]+>/","<a href='mailto:$email?subject=Re: ".$jobtitle." position. Ref:".$jobref."&body=I am interested in this position and have attached my CV.%0a%0aMy name is:%0aMy Phone Number is:%0a%0aI would like to add the following information:' class='link'>",$description) ;
$description=mysql_real_escape_string($description);

mysql_query("UPDATE livejobs
SET
description='$description'
WHERE 
description='$initialdescription'") or die("error in update");
}

I tried it without the WHERE bit of the UPDATE statement and it entered the same information into every row. Ouch! Oops! thank God I backed it up first!

$description = large job descriptions in html.

 

If anybody can point me in the right direction I'd appreciate it!

Ian.

[fenway]

Echo the sql statement that fails.

[defeated]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'font-weight:bold;'> Pharmacy Technician

 

As a' at line 5

I thought the real_escape_string should sort any problems with ' " etc?

 

[fenway]

That's the error, not the statement....

[defeated]

<p style=\'text-align:center;\'><span style=\'font-weight:bold;\'> Pharmacy Technician <br /><br /></span>As a part of the dispensary team the technician will assist in the day to day running of the dispensary <br /><br />

 

That's the relevant source created Fenway. What's the problem with it? I don't get it.

[fenway]

What's wrong is that I don't see the mysql statement that you're sending to the server that's causing the error....

[defeated]

At the risk of appearing even more inept......

mysql_query("UPDATE livejobs
SET
description='$description'
WHERE 
description='$initialdescription'") or die(mysql_error());

Is that not it? It's in the while loop.

 

[fenway]

Nope... that's php code... echo that string.

[defeated]

Sorry, with you now.

This is the first problem statement...

UPDATE livejobs
SET
description='<p style=\'text-align:center;\'><span style=\'font-weight:bold;\'> Pharmacy Technician <br /><br /></span>As a part of the dispensary team the technician will assist in the day to day running of the dispensary <br /><br />Reporting To Reporting to Pharmacy Manager / Dispensary Manager <br /><br /><br /><span style=\'font-weight:bold;\'>Key Responsibilities <br /></span><br />Preparing prescriptions <br />O.T.C Sales <br />Customer Service <br />Stock control &amp; ethical ordering <br />Prescription control and paperwork <br />Dispensary house keeping <br />Pro active in personal development <br /><br /><span style=\'font-weight:bold;\'>Knowledge and Experience <br /></span><br />Must hold an approved Pharmacy Technician Qualification <br />Previous O.T.C Sales Experience <br />In-depth product knowledge <br />Ability to deal with high volume dispensing <br />Excellent attention to detail <br /><br /><span style=\'font-weight:bold;\'>Personal Attributes <br /></span><br />Have excellent interpersonal skills <br />Must be highly organised <br />Must be competent and able to work under pressure <br />Customer focused <br />Ability to work in a confidential environment <br />Keen interest in retail Pharmacy <br /><br /></p>'
WHERE
description='<p style='text-align:center;'><span style='font-weight:bold;'> Pharmacy Technician <br /><br /></span>As a part of the dispensary team the technician will assist in the day to day running of the dispensary <br /><br />Reporting To Reporting to Pharmacy Manager / Dispensary Manager <br /><br /><br /><span style='font-weight:bold;'>Key Responsibilities <br /></span><br />Preparing prescriptions <br />O.T.C Sales <br />Customer Service <br />Stock control &amp; ethical ordering <br />Prescription control and paperwork <br />Dispensary house keeping <br />Pro active in personal development <br /><br /><span style='font-weight:bold;'>Knowledge and Experience <br /></span><br />Must hold an approved Pharmacy Technician Qualification <br />Previous O.T.C Sales Experience <br />In-depth product knowledge <br />Ability to deal with high volume dispensing <br />Excellent attention to detail <br /><br /><span style='font-weight:bold;'>Personal Attributes <br /></span><br />Have excellent interpersonal skills <br />Must be highly organised <br />Must be competent and able to work under pressure <br />Customer focused <br />Ability to work in a confidential environment <br />Keen interest in retail Pharmacy <br /><br /></p>'

 

But a better example of what I'm trying to do is the following job..

SET
description='<br />\r\n<p style=\'text-align:center;\'><span style=\'font-weight:bold;\'>Supervising Pharmacist - Letterkenny<br /><br /></span>New opening planned for May 2008.<br />Requires a 3yrs+ pqe.<br />Must be TCD, Australian or New Zealand trained.<br />This Pharmacist will have a keen focus on patient counseling and advice<br />A strong business acumen<br /><br />Flexible hours and a generous renumeration package are on offer to the right Candidate.</p>\r\n<p style=\'text-align:center;\'><span style=\'font-weight:bold;\'>Send your CV in confidence to Jackie@jackiebrownmedical.ie or call 01 201 6363 </span></p>\r\n<p style=\'text-align:center;\'><span style=\'font-weight:bold;\'>\r\n\r\n<span style=\'font-weight:bold;\'>Jackie Brown Medical also recruit for Medical Sales, Social Care, Pharmacy, Allied Health, Medical Admin, Nurses and Doctors</span><span style=\'font-weight:bold;\'>. </span></p>'
WHERE
description='<p> </p>

<p align="center"><strong><span style="text-decoration: underline;">Supervising Pharmacist - Letterkenny<br /><br /></span></strong>New opening planned for May 2008.<br />Requires a 3yrs+ pqe.<br />Must be TCD, Australian or New Zealand trained.<br />This Pharmacist will have a keen focus on patient counseling and advice<br />A strong business acumen<br /><br />Flexible hours and a generous renumeration package are on offer to the right Candidate.</p>
<p align="center"><strong><span style="font-size: 10pt; color: windowtext; font-family: 'Verdana','sans-serif';">Send your CV in confidence to Jackie@jackiebrownmedical.ie or call 01 201 6363 </span></strong></p>
<p><span style="font-size: 10pt; color: #666666; font-family: 'Verdana','sans-serif';">
<p align="center"> </p>
<strong><span style="font-size: 10pt; color: green; font-family: 'Verdana','sans-serif';">Jackie Brown Medical also recruit for Medical Sales, Social Care, Pharmacy, Allied Health, Medical Admin, Nurses and Doctors</span></strong><span style="font-size: 10pt; color: green; font-family: 'Verdana','sans-serif';">. </span></span></p>'

 

Sorry It's a long post. 

[bluejay002]

bout the first problem statement:

 

I think the SET was fine since quotes were slashed properly but not on the WHERE field... for the WHERE, you should employ addslashes() for the values you are inserting to avoid such errors and/or use htmlentities() to change unnecessary characters to a different one.

[fenway]

As you can see, you have nested single quotes that aren't escaped... I'm guessing that because you assigned the result of mysql_real_escape_string to a variable, PHP simply stripped to escaping backslashes.

 

Try:

 

mysql_query("UPDATE livejobs
SET
description='".mysql_real_escape_string($description)."'
WHERE 
description='.mysql_real_escape_string($initialdescription).'")" or die(mysql_error());
[code]

[/code]

[defeated]

Bluejay, I was looking at your signature and thinking about 'mine own understanding' and what it is currently lacking!

My initial reaction to your mail was... "HUH??????"

So I decided to go for a long walk on the beach with my dog. I was thinking about your post and it all became clear!

It also clearly became clear to Fenway although I suspect a long walk was not necessary in his case. I just had to apply mysql_real_escape_string() to $initialdescription so that $initialdescription==description in the db!

Simple if you know what you're doing I suppose.

Thanks guys.

Topic solved!