Thanks to everyone who has posted comments in recent days. I’m currently travelling overseas, back at my desk on 22 September, and that’s when I’ll take the quiet time I need to try out the various suggestions below – and to comment where needed. Again, thanks to all of you — and expect us to post more problems and issues here after 22 September.
Why so quiet suddenly?
11 September 20087. Formatting complex MySQL query in PHP
7 September 2008Ever 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!
6. Escaping smart quotes
3 September 2008A lot of the websites we’re linking to are using smart quotes — single and double. For example, this story on the website of the AFL-CIO has several them in the headline. When one of our correspondents simply copies and pastes the headline, this is what appears:
Don’t Miss Labor Day’s ‘Escape to the Wild’ Marathon
But once it’s in our database, it’s being shown as:
Don’t Miss Labor Day’s ‘Escape to the Wild’ Marathon
What’s the best way to change all smart quotes, single and double, automatically into characters that will not break in our new database? Because this should be done as the records are entered, it would need to be in Perl, which is what we’re currently using for database entry.
Thanks!
5. Strange behaviour on our new Farsi (Persian) page
3 September 2008First of all, you don’t need to know Farsi to help out here. I don’t understand Farsi myself, but the problem is obvious when you look at the page. Here’s a screenshot of the problem:
The problems are in the first and last rows. Reading from right to left (and the HTML header instructs the page to be read from right to left), you should see the country name, the header (in Farsia), the date and then in brackets, the source.
Here is the HTML header:
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fa" lang="fa" dir="rtl">
As you can plainly see, the first line is screwed up because the closing bracket for the source (IASWI) goes after the final field, which is a link to a campaign and is highlighted in yellow.
The last line is even worse – it puts the source first, the country last. This should have nothing to do with the fact that the text is in English — because it’s tagged as being in Farsi (the link actually takes you to a page with both Farsi and English text).
Here is the PHP code for generating this line on our page:
echo (‘<a href=”http://www.labourstart.org/cgi-bin/show_news.pl?country=’);
echo $countryen;
echo (‘”><b>’);
echo $country;
echo (‘</b></a> ‘);
echo (‘<a href=”‘);
echo $url;
echo (‘” title=”‘);
echo $userid;
echo (‘”>’);
echo $header;
echo (‘</a> ‘);
if ($row2[‘actnowcampaigncode’] > 0) {
echo (‘ <a href=”http://www.labourstart.org/cgi-bin/solidarityforever/show_campaign.cgi?c=’);
echo $actnowcampaigncode;
echo (‘”><span style=”background-color:yellow;color:black”><b>Act</b><i>NOW!</i></span></a> ‘);
}
echo $dd;
echo (‘-‘);
echo $mm;
echo (‘-‘);
echo $yyyy;
echo (‘ [‘);
echo $source;
echo (‘] ‘);
Anyone have any ideas about how to fix this? Thanks.
4. Pound sign breaking database [SOLVED]
3 September 2008
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]
3 September 2008CASE 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]
3 September 2008CASE 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();
1. Conversion of text from Unicode – problem with Perl Text::Iconv
3 September 2008LabourStart has recently converted its news links database to Unicode. As we now work in 22 languages, it’s important that we be able to show characters correctly — including in our lists of languages displayed at the top of every page on the site.
But … most union websites don’t use Unicode. And the JavaScript newswires we created, which now generate Unicode characters, were causing problems. So we’ve gone into the script which creates the JavaScript every 30 minutes and told it to convert Unicode back into character encodings like iso-8859-1 (for Western languages), windows-1251 (for Russian), etc.
The problem is, while this works like a charm on the Russian, it’s not working on Norwegian — or not converting all the characters, not even all the comon ones.
Here is a page showing the current Norwegian JavaScript newswire. To see the characters correctly displaying in Unicode, go here.
We’re using a Perl module – Text::Iconv to do this. Here is the code:
if ($langcode eq “no”) {$converter = Text::Iconv->new(“utf-8”, “iso-8859-1”);}
$Header = $converter->convert(“$Header”);
Can anyone help sort this out for us?
Labourstart Techblog 