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!
7 September 2008 at 3:28 pm |
I don’t know if this is what’s causing the problem, but I’ve always used the syntax “LIMIT 0,#” to get # items (and seen it that way in examples). So you might try changing the end of your query to
LIMIT 0,$totalstories
It might also help if you posted more specifics about how it is not working (is it not returning a valid result, or too many, etc.???)
7 September 2008 at 8:00 pm |
You don’t mention what the ‘problems’ are? Do you get an error message? Does it output unexpected content, or is it the wrong order? etc., etc.
7 September 2008 at 11:12 pm |
Hi,
If I understand correctly you are trying to retrieve records where the priority may have multiple values (ie priority of 2 and a priority of 3)
Your variable $ipriority will need to be an array if you want it to hold 2 separate priority values or you could use 2 variables maybe. If I am correct in my understanding of the problem then in the function you will need to then retrieve the two values from the array and modify the query to use “in” instead of “=” :
WHERE priority in (value 1, value 2) AND language …. etc etc
Rather than:
WHERE priority = $ipriority ……
8 September 2008 at 9:00 am |
SELECT *
FROM news
WHERE priority BETWEEN 2 AND 3
AND length(language_code) BETWEEN 2 AND 3
ORDER BY date DESC, time DESC
—–
That’s the MySQL query. You will need to insert the correct variable names. And I suspect that date and time are found in a single datetime field. That’s fine; just make the last line “ORDER BY datetime DESC”
8 September 2008 at 12:31 pm |
I suggest that you output the value of $query directly into a test version of the web-page, then copy and paste the query string into MySQL. You should be able to see what is wrong, e.g. check that the langcode and totalstories variables have the expected values & effects.
8 September 2008 at 2:11 pm |
syntax looks fine – try running the query then
echo mysql_error();
9 September 2008 at 6:13 pm |
Can I offer a “radical” suggestion?
Why not completely move your site to a new system. My recommendation… Drupal.
I am familiar with this system and if you need any help setting it up, I’d be glad to offer my support. The reason why I suggest this is because fixing up an old system will run you into more problems.
BTW, in your SQL query and the WHERE clause, it looks like you’re using back ticks instead of single quotes. I’m not sure if it came out like this because of the text format you’re using or if that’s actually how you hard coded it.
10 September 2008 at 6:46 am |
Try this
$query = “SELECT * FROM news WHERE priority = ” + $ipriority;
$query = $query + ” AND language = ‘” + $langcode + “‘”;
$query = $query + ” ORDER BY date DESC, dateposted DESC”;
$query = $query + ” LIMIT ” + $totalstories;
Adding the variables outside of the string text may help.
11 September 2008 at 3:03 pm |
A webmaster has a problem formatting a MySQL query. He moves his website to Drupal. Now he has two problems.
12 September 2008 at 1:30 am |
If you have problems, feel free to contact me directly. Email me through our union website and i should be able to assist. Check out our site, we run php/mysql for our site. http://www.gftunion.com Just click the contact us link and let me know. If you send me an email, ill give you my chat username and i can help at anytime. Good luck.
12 September 2008 at 2:54 am |
Hi,
Yes, the full text of $query would be helpful in debugging, as would the mysql_error() output.
My steps in debugging these issues is to printout the full query in the html ( echo ““, then to cut and paste the query into phpMyAdmin.
That gives me very quick and straightforward problem solving access. I can then play with the SQL until I get the result I think I should have received, and then plug the corrected version back into the code.
Jason Cleeland
Open Source Surveys for Unions? http://www.limesurvey.org
10 October 2008 at 7:21 pm |
I’m new to PHP and recently setup my local machine with PHP and MySQL for doing development. I was sort of stuck when I needed to post my work for the user to test and review. After looking around a bit I found a site that hosts PHP and MySQL apps. I was surprised that it was free – it seems they’re offering the service at no cost until 2012. At that point they’ll change over to a fee-based service. However, in the meantime, it’s a great place to do anything from demo and sandbox right up to posting sites for real.
Their pitch is as follows:
“This is absolutely free, there is no catch. You get 350 MB of disk space and 100 GB bandwidth. They also have cPanel control panel which is amazing and easy to use website builder. Moreover, there is no any kind of advertising on your pages.”
Check it out using this link:
http://www.000webhost.com/83188.html
Thanks and good luck!