4. Pound sign breaking database [SOLVED]

CASE CLOSED: This is a strange one.  I couldn’t see any reason why the £ would not appear, and now see that it is appearing.  To answer Chris’ comment below, yes, the table is in Unicode and the page where the £ would have been entered is Unicode, so we’ll just assume this was some kind of odd glitch …

In our new MySQL database to which we are adding records using a Perl script, the entire thing now in Unicode, keying in the sign for pounds sterling (£) is resulting in an error character.  This may be due to it being enclosed in apostrophes in our script.  The field is called header and the variable being added to it is $header.  Can you help us fix this?  Thanks.

The relevant line is here:

$db->do(“INSERT INTO news (url, header, country, country2, date, priority, userid, source, formoreinfo, language, regreqd, keywords, image, actnowcampaigncode, state, dateposted) VALUES (‘$url’, ‘$header’, ‘$country1′, ‘$country2′, $date, $priority, ‘$userid’, ‘$source’, ‘$formoreinfo’, ‘$language’, ‘$regreqd’, ‘$keywords’, ‘$image’, ‘$actnowcampaigncode’, ‘$state’, $date)”) || die (print qq|<span style=”color: red; font-weight: bold; background-color: yellow; padding: 5px; border: 1px red dotted”>Could not add record.</span>|);

3. A simple MySQL sorting question [SOLVED]

CASE CLOSED.  The solution was partially implemented and is now fully working.  There is a MySQL field called dateposted.  I have changed this to be a DATETIME field.  The sort is now by date DESC, dateposted DESC.  This should all work.  Thanks for getting those grey cells working!

In the PHP code for the page which shows our news in Norwegian, we sort in descending order based on the date field.  The problem is that this means that the newest items added to the database  within a single date are moved to the bottom.  How can we keep the newest items on top?

Here is the code we’re using:

$query2 = “SELECT * FROM news WHERE language = ‘no’  order by date DESC LIMIT 100″;

2. How to allow apostrophes to be added to fields in MySQL database using PHP? [SOLVED]

CASE CLOSED: Thanks to people for pointing out how PHP would have solved this.  A little bit of web searching revealed that there’s a simple solution in Perl as well — and it turns out that this was partially implemented.  We allowed apostrophes in the story headlines, but not in the source.  It was a simple case of substitution, adding an escape character, and when we added this to the Perl code it worked.

We discovered this problem on LabourStart’s Italian page.  If we try to add a news story and in the source field key in, for example, l’Unita, the attempt is rejected.  We’re using a Perl script to do this, and the relevant lines are follow.  The field source field is called $source and you can see that the new input field is enclosed in apostrophes, which is almost certainly what’s causing the problem.  Can you help us?

Here’s the code:

use DBI;

$db = DBI->connect(‘dbi:mysql:***:***’,'***’,'***’);

if ($modify eq 1) { # delete old record first
$db->do(“DELETE FROM news WHERE url=’$url’”) || die (‘Could not delete record.’);
}
$db->do(“INSERT INTO news (url, header, country, country2, date, priority, userid, source, formoreinfo, language, regreqd, keywords, image, actnowcampaigncode, state, dateposted) VALUES (‘$url’, ‘$header’, ‘$country1′, ‘$country2′, $date, $priority, ‘$userid’, ‘$source’, ‘$formoreinfo’, ‘$language’, ‘$regreqd’, ‘$keywords’, ‘$image’, ‘$actnowcampaigncode’, ‘$state’, $date)”) || die (print qq|<span style=”color: red; font-weight: bold; background-color: yellow; padding: 5px; border: 1px red dotted”>Could not add record.</span>|);

$db->disconnect();