Thread: Need PHP/SQL code help...to fix written code

I can appreciate the difficult situation in which you find yourself, but it's quite hard to help a non-programmer to debug a program at a distance.

From what I can see, there's nothing that limits the items that are displayed, besides the one-year and four-year past dates. However, you have only shown a part of the script, and significantly, not the code that inputs new items.

In situations like this where a program has been working for a long time, but suddenly doesn't work properly, the thing to ask is What changed? Computer programs don't take whims and stop working. Something always has changed. Try to determine what has changed.

As a wild, outside guess, my first thought is that the 3 new entries never actually got entered in the database, or were entered without the appropriate date value in the field "datetime" (which, by the way, is really bad practice, since that is a keyword in PHP, and shouldn't be used as the name of a field in a table. But since it has been working for several years, it's apparently not causing a problem in your script). If you have the ability to look at your data, perhaps using phpMyAdmin or a similar database utility, the first thing to do would be to see if the data ever got entered in the table.

Okay, I thought I should post an image that shows database entries. The collection_dt has never been used consistently, some of the entries show NULL, yet they work. I've tried changing the datetime to an earlier date, just a few months. And, tried matching the collection_dt to the same date.

Do I need to show any include files?

I wondered if the table was limiting to 9 entries.

On the image, the rows for 103,104, and 105 are the ones that don't show. The entries for 100, 101, and 102 do work. Row 104 is the entry that I modified on datetime collection_dt. Normally, I don't put anything in the collection_dt the reason it shows NULL-- but then those rows worked!

This code has been used for a few years and I've been putting in the entries for about one year... not quite one year. For all I know, it's been broken in the past, but no one else noticed. I always check the pages after I do database entries....

If you can help... I'll post whatever you need. It is a volunteer job, no money to the website. We are bikers and any money raised is for local charities (kids, shelters, etc)

Thanks in advance... really, thanks!!

Ahh! So what has changed is that you entered a date value in the 3 records where all the others have no value (NULL). And those are the records that are not being displayed.

I think the issue is in the name of the column (field) that you are referring to as collection_dt. That's fine, if that's the name of the column that contains some null values. But that's not what the SQL query in your script is looking for. It now says (in 2 places):

Code:

WHERE datetime BETWEEN date_add(now(), interval -365 day) AND now()

but it appears that it should say:

Code:

WHERE collection_dt BETWEEN date_add(now(), interval -365 day) AND now()

This would also resolve my earlier criticism that "datetime" was being used as a column name. The mystery is how it could have worked all this time with that error. Perhaps because nobody was using that field, I'm not sure.

Again, it's hard to guide someone who is inexperienced with PHP in debugging a script, but you might want to try making the above substitution in both places where the SQL string ($sql) is being defined. Just to make sure you don't create an even bigger problem, though, you should first make a backup copy of the script (perhaps name it xxxxx.php.bu, or something).

I'm kind of groping in the dark, though, since I don't have the ability to see what the names of the columns in your table are. The above suggestion is based on the assumption that the column where some records have a NULL value is actually named "collection_dt". "Datetime" is a data type that determines what kind of data is stored in a column.

As a temporary fix, I would first try removing the date values from those 3 records to see if they then appear.

I should have thought of including the column headers... sorry about that. So, here's another image.



I don't normally put the date in the collection_id, but I did in the three latest in an effort to make them appear. I've taken the date out again, and the entry shows null.

I've made a backup copy of the script and I'll make the edit you suggested. I hope it works....

What does this mean?

$sql = "SELECT collection_name, collection_desc, collection_link, photographer,

date_format(collection_dt,'%b %e, %Y') AS collection_dt FROM photos ";



I understand the select for column names, but it seems to be converting to something and being used as the collection_dt?

I thought maybe I should show you the settings on the database. Image one is for the entry.... image two is the database.

Don,

I created a test .php page. I changed the datetime to be collection_dt in four places, because the sort was set to datetime.

$sql .= "WHERE collection_dt BETWEEN date_add(now(), interval -1460 day) AND date_add(now(), interval -365 day)";
$sql .= "ORDER BY collection_dt DESC";

That resulted in blank results in the current section. Archived section seems to be the same. So, I changed the order by back to datetime.

Still blank results in the current section. Archived seems to be the same.

This is only on my test page, which is a edited copy of the current.

Originally Posted by jmurrayhead

It's taking the value from the column named "collection_dt" and converting the date format to this: %b %e, %Y

It is then returning that new formatted date with an alias, which is also called "collection_dt".

Like an alias? Seems like it could conflict.