N00b needs help with Multidimensional Array pulled out of mysql Table!

[HoofSC]

Hi,

 

I'm not sure if my issue, or the fact I need to be thorough explaining myself frustrates me more at this point. Every step of the way I want to pull my hair out - guess I should've gone to school for this shit.

 

Here is what I have setup:

 

a mysql database with a table called Addresses. Three of the custom fields in this table for each item listed (inidividual store fronts) that are important to me are 1. the episode number, 2. the location title for the episode mentioned, 3. the store front title for the location title...

 

Here is what I would like to do:

 

1. create a php file that connects to the db. -done, got this

 

2. select my table with the store fronts and order those listings by a column named "addyEpisodeNumber". The way these are labeled and entered into the fields are as tinytext and in the form of (Episode 101, Episode 102, Episode 103, etc.) I assume that I can simply use "select * from Addresses ORDER BY addyEpisodeNumber" for my query. -Is this correct?

 

3. At this point I would have in my query results all of the store front entries. Correct?

 

4. Now I want to loop through my results and I want to make a MULTIDIMENSIONAL ARRAY in the form of

 

$addressArray[0-n] = array("Episode 101", "Restaurant", "Hawaiian BBQ");

where I am calling in from my query the column names as follows:

$addressArray[0-n] = array("addyEpisodeNumber", "addyLocationTitle", "addyTitle");

 

-Make sense? Sorry I'm so poorly explaining.

 

Then what I would like to do is be able to specify the items from the array. In the above example (and further down on my php script) I would like to implement:

 

$addressArray[0][0] as "Episode 101"

$addressArray[0][1] as "Restaurant"

$addressArray[0][2] as "Hawaiian BBQ"

- Can anyone help me with my query, sort and processing loop (foreach?, for?, while?) in order to build this MDarray from a simple query drawing in 3 of the important fields????

 

Any help would be greatly appreciated. Thanks in advance!

 

n00b

 

 

 

 

 

[GingerRobot]

At present, your sorting may well cause you a problem. If your episode numbers are not all 3 digits, then you have an issue. You would be much better off just storing the episode number in an INT field, and dropping the text 'episode'. Apart from anything else, its constant - so its a waste of storage space.

 

For example, if you were to have the episodes: 101 and 99, then 99 would be sorted AFTER 101 - because the column is being sorted as a string.

 

Now, you may be able to do some regex and just sort by the numbers, but it would be far easier (and the query woudl be quicker) to drop the 'episode' text and use a INT field.

 

As for the rest, its fairly straight forward:

 

<?php
//connect to database
$sql = "SELECT addyEpisodeNumber,addyLocationTitle,addyTitle FROM Addresses ORDER BY addyEpisodeNumber";
$result = mysql_query($sql) or die(mysql_error());
$x = 0;
while($row = mysql_fetch_row($result)){
    $array[$x] = $row;
    $x++;
}
echo $array[0][2];//the first row's title
echo $array[12][0]; //the 12th row's episode number
?>

[HoofSC]

Ah, thanks for the sorting pointer. I will keep that in mind as I continue building my scripts.

 

Fairly straightforward indeed. Thank you for a prompt and informed response ginger!

 

Another "sorting" problem with the way I have the database setup is now for me to attempt to implement a tree-like structure for my information.

 

For example: It would greatly help my program if I were able to somehow COMPARE the field values for each "store front" (those important 3 columns, and somehow arrange my php script to simplify it's categorization by creating addtional MDarrays...

 

Such as:

 

-Episode 101

    (Restaurant)

        o Hawaiian BBQ

-Episode 102

    (Office)

        o Dave's Office

        o Mike's Office

    (Diner)

        o Deloris Cafe

-Episode 103

    (Gym)

        o Bally's

        o 24 Hour Fitness

 

I know I'm going for an extra stretch here in creativity in PHP/mySql, but I'm sure there must be a simple way to construct easy-to-use arrays.

 

What I'm eventually trying to do is have this PHP page's output be a "Chained Selection" content listing. And have my eventual PHP output appear as the following source: (This example uses a vehicle manufacturer, make, and model to populate "Chained Selection" forms on a separate .html page by including this page as "content.js"

 

// define a list group
addListGroup("vehicles", "makers");

// and its top list
addList("makers", "Toyota", "Toyota", "toyota-list");
addList("makers", "Honda", "Honda", "honda-list");
addList("makers", "Chrysler", "Chrysler", "chrysler-list");

// sub-list for toyora
addList("toyota-list", "Car", "Car", "toyota-car");
addList("toyota-list", "SUVs/Van", "SUVs/Van", "toyota-suv/van");
addList("toyota-list", "Trucks", "Trucks", "toyota-truck");

// sub-list for honda
addList("honda-list", "Car", "Car", "honda-car");
addList("honda-list", "SUVs/Van", "SUVs/Van", "honda-suv/van");

// sub-list for chrysler
addList("chrysler-list", "Car", "Car", "chrysler-car");
addList("chrysler-list", "SUVs/Van", "SUVs/Van", "chrysler-suv/van");

// sub-list for toyota car
addOption("toyota-car", "Avalon", "Avalon");
addOption("toyota-car", "Camry", "Camry");
addOption("toyota-car", "Celica", "Celica");
addOption("toyota-car", "Corolla", "Corolla");

// sub-list for toyota suv/van
addOption("toyota-suv/van", "4Runner", "4Runner");
addOption("toyota-suv/van", "Highlander", "Highlander");
addOption("toyota-suv/van", "Land Cruiser", "Land Cruiser");
addOption("toyota-suv/van", "Sienna", "Sienna");

// sub-list for toyota truck
addOption("toyota-truck", "Tacoma", "Tacoma");
addOption("toyota-truck", "Tundra", "Tundra");

// sub-list for honda car
addOption("honda-car", "Accord", "Accord");
addOption("honda-car", "Civic", "Civic");
addOption("honda-car", "S2000", "S2000");

// sub-list for honda suv/van
addOption("honda-suv/van", "CR-V", "CR-V");
addOption("honda-suv/van", "Pilot", "Pilot");
addOption("honda-suv/van", "Odyssey", "Odyssey");

// sub-list for chrysler car
addOption("chrysler-car", "PT Cruiser", "PT Cruiser");
addOption("chrysler-car", "Sebring", "Sebring");

// sub-list for chrysler suv/van
addOption("chrysler-suv/van", "Town & Country", "Town & Country");
addOption("chrysler-suv/van", "Voyager", "Voyager");

 

Any further ideas for constructing those multi-dimensional arrays would be awesome.

 

Thanks again gingerRobot!

 

 

 

[craygo]

I used my own field names for testing but you can try something like this.

<?php
$addressArray = array();
$sql = "SELECT addyEpisodeNumber,addyLocationTitle,addyTitle FROM Addresses ORDER BY addyEpisodeNumber";
$res = mysql_query($sql) or die(mysql_error());
$i= -1;
$j=0;
$last_episode = "";
while($r = mysql_fetch_assoc($res)){
  if($r['addyEpisodeNumber'] != $last_episode){
  $j=0;
  $i++;
  $addressArray[$i][$j] = $r['addyEpisodeNumber'];
  $j++;
  }
$addressArray[$i][$j] = $r['addyLocationTitle'];
$j++;
$addressArray[$i][$j] = $r['addyTitle'];
$j++;
$last_episode = $r['addyEpisodeNumber'];  //
}
?>

 

now the array index will only change when the episode changes

 

Ray

 

[HoofSC]

Thanks Ray, for some more creativity.

 

Could you walk me through your while loop? And I'm such a noob that without an echo or trace type output, I'm not sure how the addressArray variables are now looking (i.e. that parent, child tree structure in my above post) or how I can call each item individually now?? Am I making sense in trying to construct that content.js using this PHP/mysql code?

 

Thanks again

 

 

[craygo]

Here is a new one with the results outputted and note for the code

<?php
// start an empty array
$addressArray = array();
// sql statement, make sure you order you results by the field you want to be your header or group
$sql = "SELECT addyEpisodeNumber,addyLocationTitle,addyTitle FROM Addresses ORDER BY addyEpisodeNumber";
$res = mysql_query($sql) or die(mysql_error());
// Set initial value for our keys. We start at -1 because we will be incrementing the numbers before setting them Episode.
$i= -1;
// Set initial value for second set of keys
$j=0;
// Set the initial episode
$last_episode = "";
// start our loop
while($r = mysql_fetch_assoc($res)){
  // Check to see if the episode hase change (sort of like grouping)
  if($r['addyEpisodeNumber'] != $last_episode){
  // if the episode has chenged then we reset $j to 0
  $j=0;
  // Now we add one to $i so we get a new group number, so to speak
  $i++;
  // Add the episode to the array. Episode will always be [x][0] where x will be a sequential number everytime the episode changes
  // Example episode 1 will be $addressArray[0][0], Episode 2 will be $addressArray[1][0] and so on
  $addressArray[$i][$j] = $r['addyEpisodeNumber'];
  // We add 1 to j so we can continue adding to the group
  $j++;
  }
// add the Location title to the array
$addressArray[$i][$j] = $r['addyLocationTitle'];
// Add 1 to $j
$j++;
// Add addyTitle to the array
$addressArray[$i][$j] = $r['addyTitle'];
// Add 1 to $j
$j++;
// set last_episode to the current Episode to recheck it at the start of the loop
$last_episode = $r['addyEpisodeNumber'];
}
// count the number of entries in the array
$count = count($addressArray);
// start a loop for the number of entries
for($x=0; $x<$count; $x++){
  // loop through the addressArray[]
  foreach($addressArray[$x] as $key => $value){
  echo '$addressArray['.$x.']['.$key.'] = '.$value."<br />";
  }
}
?>

 

Ray