7. Formatting complex MySQL query in PHP

Ever since LabourStart was founded we’ve had top priority news stories in English followed by lesser priority ones, but have never done this for our other 21 languages.  Until now.

Now we have the capacity to do this and I’ve added some lines of code to our PHP script that generates our front pages — but it’s not working.  I run a PHP function (readdb) twice, like this:

readdb (3,5);
readdb (2,100);

The first number is the story’s priority (taken from our database) and the second is the maximum number of stories to show.

Then I call the function like this:

function readdb ($ipriority,$totalstories) {

But it’s not working as I’d hoped.

This is really a matter of getting the syntax right — and nothing I’m trying seems to work.

I want to tell the system to get all records from a table called “news” where the priority is either 2 or 3 and where the language code is a 2 or 3 character code, and then to sort by date and within date, by the date and time the story was entered into our database.  This is the line that’s causing all the problems — remember that priority is a numeric field and language is text:

$query = “SELECT * FROM news WHERE priority = $ipriority AND language = ‘$langcode’ ORDER BY date DESC, dateposted DESC LIMIT $totalstories”;

Can anyone help?  Thanks!

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();