greens85 Posted September 24, 2010 Share Posted September 24, 2010 Hi all, I'm having a hard time with strings... I am pulling data from an XML feed, and then trying to insert into my database. All the code is wrote, the problem is that some of the descriptions contain both ' and " so it is messing with my insert statement. I can get over the ' by doing this: \"$newstring\" however it then fails on " I have tried every function I can think of: str_replace, mysql_real_escape_string, htmlspecialchars, addslashes, stripslashes etc. etc. nothing seems to do the trick! I have attached the code in full, would someone be able to take a look and help me out? Any help would be greatly appreciated! many thanks Greens85 [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/ Share on other sites More sharing options...
PFMaBiSmAd Posted September 24, 2010 Share Posted September 24, 2010 It would be helpful if you echoed $query for a value that is failing and post the actual query along with the actual msyql_error() message that you get for that query. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1114993 Share on other sites More sharing options...
greens85 Posted September 24, 2010 Author Share Posted September 24, 2010 Hi, Thanks for your prompt reply! The query is long... as it is inserting all until it encounters a ' or "; So I am just going to post the SQL error: 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 's experience teaching. The pupils are in KS3 and KS4 (up to GCSE) and have Emot' at line 3 If you want to see the result of the echo you can do so here: http://www.beta.educationvacancies.com/AXCIS_xml_feed.php Many thanks Greens85 Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1114995 Share on other sites More sharing options...
BlueSkyIS Posted September 24, 2010 Share Posted September 24, 2010 How I write an SQL statement with $a_string containing ' and/or ": $a_string = mysql_real_escape_string($a_string); $sql = "INSERT INTO some_table VALUES ('$a_string')"; mysql_query($sql) or die(mysql_error() . " IN $sql"); Note: I double-quote the entire SQL and single-quote ALL values (numeric and text). Using mysql_real_escape_string ensures that the value will work in single-quotes. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1114999 Share on other sites More sharing options...
greens85 Posted September 24, 2010 Author Share Posted September 24, 2010 Hi BlueSkyIS, Many thanks for your response! Sorry I have misunderstood this but isn't that what I'm doing anyway just in a different manner: // Variable is in mysql_real_escape_string $newstring = mysql_real_escape_string($item->description); // Entire query is wrapped in double quotes and value variables are wrapped in single quotes $query = "INSERT INTO jobs (jobid, jobref, employerid, position, category, country, subcounty, contract, hour, salary, description, start, postdate, cname, tel) VALUES ('$jobid', '$reference', '117', '$title', '$category', '$country', '$location', '$jobtype', '$hours', '$salary', '$newstring', '$start', '$EXmonth/$EXday/$EXyear', '$contactname', '$contacttel')"; $result = mysql_query($query) or die (mysql_error()); I'm not great as PHP (hence my post) so if I have misunderstood I am sorry. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115001 Share on other sites More sharing options...
BlueSkyIS Posted September 24, 2010 Share Posted September 24, 2010 On the sample page, the error at the bottom does not correspond to the SQL above. Can you update the mysql_error() to show the actual SQL that is being executed? mysql_query($sql) or die(mysql_error(). " IN THIS SQL: $sql"); Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115004 Share on other sites More sharing options...
greens85 Posted September 24, 2010 Author Share Posted September 24, 2010 I'm not sure why... I have 100% made no changes to the code since I uploaded and ran that test! Could the error be coming from something else [EDIT] Sorry misunderstood you post... will make the changes now! Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115007 Share on other sites More sharing options...
BlueSkyIS Posted September 24, 2010 Share Posted September 24, 2010 mysql_query($somesqlhere) or die(mysql_error(). " IN THIS SQL: $somesqlhere"); Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115008 Share on other sites More sharing options...
BlueSkyIS Posted September 24, 2010 Share Posted September 24, 2010 It appears that the value for description has not been mysql_real_escape_string'ed Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115009 Share on other sites More sharing options...
greens85 Posted September 24, 2010 Author Share Posted September 24, 2010 I trust I have now understood correctly: My code of this: $query = "INSERT INTO jobs (jobid, jobref, employerid, position, category, country, subcounty, contract, hour, salary, description, start, postdate, cname, tel) VALUES ('$jobid', '$reference', '117', '$title', '$category', '$country', '$location', '$jobtype', '$hours', '$salary', '$newstring', '$start', '$EXmonth/$EXday/$EXyear', '$contactname', '$contacttel')"; mysql_query($query) or die(mysql_error(). " IN THIS SQL: $query"); echo $query; Produced: 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 's experience teaching. The pupils are in KS3 and KS4 (up to GCSE) and have Emot' at line 3 IN THIS SQL: INSERT INTO jobs (jobid, jobref, employerid, position, category, country, subcounty, contract, hour, salary, description, start, postdate, cname, tel) VALUES ('428', 'Axcis/031213', '117', 'Part time SEN Teacher', 'Special Needs Teacher Jobs', 'Eastern', 'Essex', 'Temporary', 'Part Time', 'Negotiable', 'A Pupil Referral Unit in West London requires a full-time Maths & ICT teacher to start after the October half-term. You must have a formal teaching qualification, and at least one full year's experience teaching. The pupils are in KS3 and KS4 (up to GCSE) and have Emotional and Behavioural Difficulties (EBD). You don't necessarily need to have previous experience working with EBD pupils, however you must have the right kind of personality - a caring and flexible nature. If you think you fit the bill, please do not hesitate to apply. You can do so through the Axcis website, or by emailing your up-to-date CV to [email protected] Pay will be in the region of ?145 per day, and only successful candidates will be contacted. ', '23 Sep 2010', '10/23/2010', 'Mari-Lize Aucamp', '@ Axcis 02075802956') Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115010 Share on other sites More sharing options...
PFMaBiSmAd Posted September 24, 2010 Share Posted September 24, 2010 The error is because your logic for $count is wrong. You only use mysql_real_escape_string() on the description when if($count > 0){ If you look at your queries, you will see that the first three queries are correct, but after that they reuse everything but the description because the if($count > 0){ logic causes the code that is setting all the other values and using mysql_real_escape_string() on the description is being skipped over. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115011 Share on other sites More sharing options...
greens85 Posted September 24, 2010 Author Share Posted September 24, 2010 Hi PFMaBiSmAd, I think I get what you are saying: Because the escaping is done inside the if... the description will only get escaped so many times? Would the solution be using a while statement instead... as I said before I'm not great at PHP so hopefully I'm at least along the right lines! Thanks Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115019 Share on other sites More sharing options...
PFMaBiSmAd Posted September 24, 2010 Share Posted September 24, 2010 Based on the comments in the code, $count is being used to limit the number of items to display/process. If that is so, the query logic should be inside of the if($count > 0){ ... } logic, not outside of it. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115021 Share on other sites More sharing options...
greens85 Posted September 24, 2010 Author Share Posted September 24, 2010 As you may have guessed i didnt 100% write this so im not sure! I will try and take your comments to fix this, if not I shall post back once I've had a go! Many thanks for everyones responses and help. [EDIT] Looking at it again, would changing the count to something higher than 3 help... let say I changed it to 1000? Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115022 Share on other sites More sharing options...
PFMaBiSmAd Posted September 24, 2010 Share Posted September 24, 2010 If you always want to process all the items, you should just remove all the lines of code related to $count, don't forget the closing } that matches the if($count > 0){ line. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1115025 Share on other sites More sharing options...
greens85 Posted September 28, 2010 Author Share Posted September 28, 2010 Hi PFMaBiSmAd, Sorry for the delayed response! I would ideally like to only process all of the items the first time it is run i.e. now... then each additional time the script runs. This will be automated on a CRON job, I only want to add items that aren't already in the database. I think my code will already achieve this, but obviously the section of code you mentioned in your previous post is failing in someway. Is is possible that rather that I could; remove the code run the script get all the items in the database put the code back in and set it on a cron, then it would add only additional items? many thanks, greens85 Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1116768 Share on other sites More sharing options...
PFMaBiSmAd Posted September 28, 2010 Share Posted September 28, 2010 The logic using $count would not do anything for detecting what is or is not already in the database. Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1116773 Share on other sites More sharing options...
greens85 Posted September 28, 2010 Author Share Posted September 28, 2010 So now I have happily imported all records, I can safely remove that logic? This is my check for existing records: $query = "SELECT * FROM jobs WHERE jobref ='$reference'"; $result = mysql_query($query) or die (mysql_error()); if(mysql_num_rows($result)==0){ // increment the jobid based on the last jobid in the database $qc = "SELECT jobid FROM jobs ORDER BY jobid DESC"; $rc = mysql_query($qc) or die (mysql_error()); $ac = mysql_fetch_array($rc); $jobid = $ac[0] + 1; } Quote Link to comment https://forums.phpfreaks.com/topic/214273-string-problems-when-trying-to-insert-to-sql/#findComment-1116785 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.