storing mysql_fetch_array arrays

clairence

If I do a DB query and then store the arrays, where some 'listings' might be deleted or unavailable, which is the "better" method of storing the arrays in PHP?

/* load the database and regsiter the variables */

$find_listings=mysql_query("SELECT * FROM ListingTest where [conditions]");
$listings_count=mysql_num_rows($find_listings);

One way:

// this should return an array of sequential indices 
for ($i=0;$i<$listings_count;$i++){
	$listing_data[$i]=mysql_fetch_array($find_listings); 
	}

Or alternatively:

// this should return an array of indices based on ListingIDs
for ($i=0;$i<$listings_count;$i++){
	$oops[$i]=mysql_fetch_array($find_listings); 
	$listID=$oops[$i][0];
	$listing_data[$listID]=$oops[$i]; 
	}

I realize that, if I write the rest of my PHP correctly, either should probably work.

But I'm wondering if there is a best practices?
Thanks.

laserlight

What exactly are you trying to do?

clairence

ListingTest table holds info on various properties.

(ListingID, UnitStreet, UnitCity, UnitState, UnitZip, UnitSize, Beds, Baths, etc...)

I write a page with some of the data that links to a page which displays full data of the selected property.

And then the new page takes the $_GET['listing'] and displays everything there is to know about the property.

So basically, I can use either the ListingID field from the DB or the index of the $listing_data array to populate the 'listing' field.

laserlight

Ah. The idea here is to use the primary key of the ListingTest table. This seems to be ListingID, and as you pointed out, if ListingID is autoincremented, the number sequence may have gaps or be out of order. As such, select the ListingID and the other fields you need in the initial list, then iterate over the result set and print:

$result = mysql_query("SELECT ListingID, UnitCity, Beds FROM ListingTest where [conditions]");
while ($row = mysql_fetch_assoc($result)) {
    $listing_data = rawurlencode($row['ListingID']);
    $UnitCity = htmlspecialchars($row['UnitCity']);
    $Beds = htmlspecialchars($row['Beds']);
    // Print the listing.
    // ...
}

Note that rawurlencode() is not necessary in this case since we only expect $row['ListingID'] to be an integer.

clairence

Thank you, laserlight.
I will play around with the code you shared, because I'm not familiar with how the while (mysql_fetch_assoc) configuration works.

What you've suggested is quite a bit different than what my solution was. So you've given me some things to think about.
A couple of things:

First, this is a table with about 25 fields. Defining each variable like this seems like a lot of work and maybe a bit bloated.
Is it okay to simply use extract() here?

Also, the code you share results in data for a single record at a time, but doesn't store it in any array for future use.
I wonder if it is better to make a db call each time a record is selected, or to store the data initially and then work through an array when a record is selected?

clairence

I might add that one issue I'm trying to accommodate is a <Previous:current:Next> navigation wrt the listings.

So, assuming there are gaps in ListingIDs due to unmet criteria, I had assumed the only way to work through a simple sequential navigation was to put them all in an array and deal with the indexes of the of the array.

Is there a simple way to identify the 'previous' and 'next' listing on the mySQL side?

(See, you got me thinking... Maybe I can figure it out myself in the meantime)

laserlight

First, this is a table with about 25 fields. Defining each variable like this seems like a lot of work and maybe a bit bloated.
Is it okay to simply use extract() here?

If you need to use all, or most, of the 25 columns, then a SELECT * would be okay. If not, you will just be retrieving unnecessary data.

Also, the code you share results in data for a single record at a time, but doesn't store it in any array for future use.
I wonder if it is better to make a db call each time a record is selected, or to store the data initially and then work through an array when a record is selected?

Make one query to retrieve the list of records (no need to put them in an array, unless you need this list for some other reason as well). When the user selects a particular record, make another query to retrieve the entire record.

I might add that one issue I'm trying to accommodate is a <Previous:current:Next> navigation wrt the listings.

You can use an array for that.

Is there a simple way to identify the 'previous' and 'next' listing on the mySQL side?

Yes. For previous, order by the ListingID, then query for the first ListingID less than your current ListingID. For next, also order by the ListingID, then query for the first ListingID greater than your current ListingID. Alternatively, retrieve all the ListingIDs in sorted order, put them in an array, search for your current ListingID, then pick the ListingIDs one before and after your current.

clairence

Make one query to retrieve the list of records (no need to put them in an array, unless you need this list for some other reason as well). When the user selects a particular record, make another query to retrieve the entire record.

If you need to use all, or most, of the 25 columns, then a SELECT * would be okay. If not, you will just be retrieving unnecessary data.

Yes, I began to understand this as I worked with the script you shared. It's clearly simpler and less weighty. Thanks.

...retrieve all the ListingIDs in sorted order, put them in an array, search for your current ListingID, then pick the ListingIDs one before and after your current.

This is what I did because it was the only thing that made sense to me.

For previous, order by the ListingID, then query for the first ListingID less than your current ListingID. For next, also order by the ListingID, then query for the first ListingID greater than your current ListingID.

This sounds intriguing, but (being a bit dense at times) I'm not sure I follow.

for example, I currently have something like this:

$selected_listing=$_GET['listing'];
$next_choice=$selected_listing+1;
$prev_choice=$selected_listing-1;

if ($next_choice>count($listing_data)){$next_choice=$selected_listing;}
if ($prev_choice<0){$prev_choice=$selected_listing;}

<?php 
if($prev_choice<$selected_listing){ echo'<span><a href="'.$prev_listing.'">&lt; Previous ]</a></span>';}
?>
<?php 
if ($next_choice>$selected_listing){ echo '<span><a href="'.$next_listing.'">[ Next &gt;</a></span>'; } 
?>

I'm sure there is a less verbose way of doing all that, and yes it's a bit redundant, but this is how I work it with an array.

can you explain how to work a query as you suggested?
thanks.