one to many relationship

one to many relationship php

[jcolgate]

Table Cars

id ¦ car
--------
1 ¦ VW
2 ¦ BMW
3 ¦ MG

Table Features

id ¦ feature
------------
1 ¦ radio
2 ¦ AC
3 ¦ Power windows

I created checkboxes and imploded the selected features for each car. I can explode the features and display them without an issue.

Now, I want to display only cars with AC as one of the selected features. I'm okay listing all cars that have the checkbox, even if they include other features.

Any thoughts?

p.s. - Thanks for this great forum! It has been a big help for me lately.

[jcolgate]

Just to clarify...

"I'm okay listing all cars that have the checkbox [AC], even if they include other features. "

[fredz]

Have you thought of listing Honda?
Very reliable.

ederf

[jcolgate]

:) Thanks, I needed a little humor today.

[cameraman]

How are you storing the info? For example:
$cars[1] = array(3);
$cars[2] = array(1,2,3);
$cars[3] = array(2);

Would mean that the vw has power windows only, the bmw has all three features, and the mg has ac only. With that structure, this would pick out the bmw and mg:
$chosen = array();
foreach($cars as $car => $features) {
if(in_array(2,$features))
$chosen[] = $car;
}

At this point $chosen array has two elements: $chosen[0] = 2 and $chosen[1] = 3
Is that close?

[jcolgate]

Sounds like it should work. But I'm missing something in the translation.

I have a table called cars, and one called features. Inside the cars I have an entry for features_id.

I query the cars:
$verify_cars = "SELECT * FROM cars";
$verify_cars_res = mysql_query($verify_cars, $conn)
or die(mysql_error());
while ($newArray = mysql_fetch_array ($verify_cars_res)){
$cars_id = $newArray['cars_id'];
$cars_name = $newArray['customer_name'];
$feature_id = $newArray['feature_id'];

// I suspect at this point I need to explode the feature_id since there will be multiple entries.

$feature_str = (explode(",",$feature_id));

}
... now this is where I get stuck. I try to insert your concept, but I end up with a foreach error.

Sorry, I don't mean to make you spell it out. You have already helped, just looking to wrap my head around it.

Thanks

[jcolgate]

Then at some point I have to pull the feature_name from that table to align it with the feature_id in the cars table.

[cameraman]

LOL I don't mind spelling stuff, I'm a good speller (well, usually)!

You're already almost there. Yes, grab out the features from the table before this segment. To test this out before running off to do that, you could temporarily set up something right now like:
$feature_friendly_text = array("nothing","radio","ac","power windows"); // First element of an array is 0, so 'nothing' is offsetting to your 'radio = 1'.
Right there after your explosion,

if(in_array(2,$feature_str)) {
// This car has feature ID #2, so display it or tag it for display
// *
} // EndIf has feature we're looking for

If this part of the script is down in the body, you can immediately echo the info you want - replace // * above with something like:
echo $cars_name;
foreach($feature_str as $onefeature)
echo $feature_friendly_text[$onefeature]; // descriptive text in array instead of id, prob from your features table?

If the script is up top, you'd probably want to insert the car's name and other desired particulars into an array, then use a foreach construct down in the body to display the vehicle list - replace // * with something like:
$car_to_display[] = array($cars_name,$feature_id); // Notice I used the unexploded one.

Then down in the body:
foreach($car_to_display as $onecar){
echo $onecar[0]; // the car's name
$feature_str = explode(",",$onecar[1]); // re-explode the features
foreach($feature_str as $onefeature)
echo $feature_friendly_text[$onefeature];
} // EndForEach car to display

If you get stuck again, don't hesitate to post again.

[jcolgate]

cameraman,

First, thank you very much for your kindness and willingness to help!

Second, man I'm still missing something. Sorry, but here is what I have:

database connect, qry cars table...

$feature_str = (explode(",",$feature_id));

$feature_friendly_text = array("nothing","radio","ac","power windows");

if(in_array(2,$feature_str)) {
// This car has feature ID #2, so display it or tag it for display
$car_to_display[] = array($cars_name,$feature_id); // Notice I used the unexploded one.
} // EndIf has feature we're looking for

}
foreach($car_to_display as $onecar){
echo $onecar[0]; // the car's name
$feature_str = explode(",",$onecar[1]); // re-explode the features
foreach($feature_str as $onefeature)
echo $feature_friendly_text[$onefeature];
} // EndForEach car to display

.................

But my output is only "radioacpower windowsac". I don't list any cars.

Let say I $_GET[feature_id] (say option 2 aka AC] in the URL. I'm hoping for:
bmw ... radio ac power windows
mg ... ac

I'm okay dressing up the output, but I'm missing the cars names. I'm thinking I need to replace the feature_friendly_text, with a qry of the features table? The "onecar" variable is confusing me.

Again, sorry - I'm sure you have tons of other things to do besides walk me through basic php.

[cameraman]

I'm sure you have tons of other things to do

LOL you'd think, but this is more interesting..

Ok, here's the whole enchilada, I've tested it and it's working here.
I created a database table called cars, field names: cars_id,customer_name,feature_id
I populated it with:
1 VW 1
2 BMW 1,2,3
3 MG 2,3

Replace the databasename, username, and password, and give it a whirl. If you get the same output as before, look very carefully at the field names in your database and make sure they match what we're
trying to retrieve.

<?php 
error_reporting(E_ALL); 
$dbn = "databasename"; 
$un="username"; 
$up="password"; 
$conn = mysql_connect("localhost",$un,$up,true) or 
die("Didn't connect."); 
mysql_select_db($dbn,$conn); 
if(isset($_GET['feature_id'])) 
$findfeature = intval($_GET['feature_id']); 
 else 
$findfeature = 1; 
$feature_friendly_text = array("nothing","radio","ac","power windows"); 
$verify_cars = "SELECT * FROM cars"; 
$verify_cars_res = mysql_query($verify_cars, $conn) 
or die(mysql_error()); 
while ($newArray = mysql_fetch_array ($verify_cars_res)) { 
$cars_id = $newArray['cars_id']; 
$cars_name = $newArray['customer_name']; 
$feature_id = $newArray['feature_id']; 
// I suspect at this point I need to explode the feature_id since there will be multiple entries. 
$feature_str = (explode(",",$feature_id)); 
if(in_array($findfeature,$feature_str)) { 
/* What we're doing here is making an array of arrays. Each $cars_to_display element contains 
 an array of two elements: the first is the car's name, and the second is its feature list. 
*/ 
$car_to_display[] = array($cars_name,$feature_id); // Notice I used the unexploded one. 
} // EndIf has feature we're looking for 
}// EndWhile  
?> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<html> 
<head> 
<title>Untitled Document</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
</head> 
<body bgcolor="#FFFFFF" text="#000000"> 
<?php 
if(is_array($car_to_display)) { 
/* Here we need to break our array up so we can display it. The foreach construct breaks an array into 
its elements. 
*/ 
foreach($car_to_display as $onecar) { 
/* Ok so after that one line there, we have one element named $onecar. As you recall, the element is 
itself an array with two elements of its own - since we didn't explicitly name them, php gave them names 
of 0 and 1. 
*/ 
echo "<div>\n"; 
echo $onecar[0] . ": <br />\n"; // the car's name 
$feature_str = explode(",",$onecar[1]); // re-explode the features 
echo "<ul>\n"; 
foreach($feature_str as $onefeature) 
echo "<li>" . $feature_friendly_text[$onefeature] . "</li>\n"; 
echo "</ul>\n</div>\n"; 
} // EndForEach car to display 
}// EndIf have car(s) matching find feature 
else echo "<p>Couldn't find any cars with that feature</p>\n"; 
?> 
</body> 
</html> 

[jcolgate]

Excellent. (To both your sticky and the last post!)

It works like a charm.

I caught the change up of cars verse customer in the array... sorry I pulled the list from another project and found my own error.

Your help has been valuable!

JC

[jcolgate]

hmm, I'm now trying to pull the feature name from the table instead of using the text friendly version.

added below $findfeature = 1;

$verify_features = "SELECT * FROM features";
$verify_features_res = mysql_query($verify_features, $conn)
or die(mysql_error());
while ($newArray = mysql_fetch_array ($verify_features_res)) {
$feature_friendly_text = $newArray[feature_name];
}

// original information $feature_friendly_text = array("nothing","radio","ac","power windows");

But the output is:
bmw:

o
w
e
mg:

w
e

I'm gathering that the letters o, w, e are part of one, or more of the entries, but it grab them instead of pulling the feature names associated with the id's.

The name of the features may change or be modified so using the text friendly won't work.

Thanks again!

[cameraman]

Yep, you're losing the array, so it's pulling a single character out of the middle of the last description. Change this:
$feature_friendly_text = $newArray[feature_name];

to this:
$feature_friendly_text[$newArray['feature_id']] = $newArray['feature_name']; // or whatever the field name is for id (to keep the array index synced to the descriptive text's record id).

[jcolgate]

And there it is... just that easy.

Cameraman, you have been tremendously helpful. PHP is addictive, you learn some, then you want more. Thanks for your help. I'll be in touch.

JC

[jcolgate]

Okay, now I'm excited.

I would like to expand this lesson if you have time.

I have CARS with FEATURES and now (thanks to cameraman) I can sort the list of cars by selecting one of many features. (Works like a charm!)

Now, let say each car has one and only one color.
Table: colors
id ¦ name
---------
1 ¦ red
2 ¦ blue
3 ¦ yellow

I want my output to be a list of all cars with AC and I would like to know what the color is of just those cars.

Example:
CAR
COLOR
FEATURES LIST

I image that I can simply pull the information just after:

while ($newArray = mysql_fetch_array ($verify_cars_res)) {
$cars_id = $newArray['cars_id'];
$cars_name = $newArray['customer_name'];
$feature_id = $newArray['feature_id'];

by saying

$color_id = $newArray['color_id'];

Then at some point later I figure I should:

$verify_colors = "SELECT color_name FROM colors WHERE color_id=$color_id";
$verify_colors_res = mysql_query($verify_colors, $conn)
or die(mysql_error());
while ($newArray = mysql_fetch_array ($verify_colors_res)) {
$color_name = $newArray[color_name];
}

But when I output the $color_name, I only get the same color for every car.

I perhaps am missing where I should pull this information and echo it out.

Thanks

[cameraman]

Micro SQL lesson:
With an SQL statement, you're telling the database what you want, from where (which table(s)) and which records.

SELECT FirstName FROM sometable
will give you all of the values for field FirstName from all of the records in the table named sometable.

SELECT FirstName,LastName FROM sometable
gets you two fields, FirstName and LastName, and all of the values.

SELECT FirstName FROM sometable WHERE LastName='Smith'
gets you one field, FirstName, but not all the records - it gets you FirstNames of all the Smiths.

SELECT * FROM sometable WHERE LastName='Smith'
gets you all of the fields from the table for all of the Smiths.

Micro PHP record retrieval lesson:
If you only expect to get one record, or only want the first of several records, from the table, you can put it into a simple variable:

$query_resource = mysql_query("SELECT * FROM sometable WHERE RecordID=1",$connection);
$data = mysql_fetch_row($query_resource);
This gets you all of the fields for record #1 into a variable called 'data'. mysql_fetch_row() returns an array of values, with the array index being an index from 0 to the number of fields - 1.
So if record number 1 is for Fred Smith and the first field in the table is called FirstName, then
$data[0] = 'Fred'
If the second field is called LastName, you guessed it,
$data[1] = 'Smith'

You can use a different function to get data that might be a little easier to remember so that you don't have to memorize the table layout:
$data = mysql_fetch_array($query_resource);
Now the variable array is indexed by field name:
$data['FirstName'] = 'Fred'
and
$data['LastName'] = 'Smith'

When you want more than the first record (and there may only be one depending on the query), you have to make allowances for that. Let's say you use SELECT * FROM sometable WHERE LastName='Smith' and you have twenty smiths in your table. You set up a control loop to capture all twenty:
$i = 0;
while($got_a_record = mysql_fetch_array($query_resource)) {
$data[$i] = $got_a_record;
$i++;
}
When the database runs out of Smiths, it returns FALSE, so execution drops out of the while statement.
Now if the first Smith is Bob Smith and the second one is Fred Smith:
$data[0]['FirstName'] = Bob
$data[0]['LastName'] = Smith
$data[1]['FirstName'] = Fred
$data[1]['LastName'] = Smith

One last thing. Even if your query only returns one field, both of those functions will give you the result as an array, not as a single value.

Armed with this new knowledge, look back over your stuff. I'll come back later to see what you learned <grin>!

[jcolgate]

Excellent tutorial...

If I pull the third value here:
$car_to_display[] = array($cars_name,$feature_id,$color_id);

and display it as:
echo $onecar[2];

I get the appropriate number of the color.

Lets say I'm looking for all cars with AC aka feature_id=2, the output is:
bmw
1
radio
ac
power windows

mg
3
ac
power windows

I believe I understand this because the data pulled is, for a lack of better terms, horizontal in the array. Or ...
car=2, feature=2, color=1.
the next record found
car=3, feature=2, color=3.

Right?

Now, here is the part I'm still working on.
If I pull
$query_resource = mysql_query("SELECT * FROM colors WHERE color_id=$color_id",$conn);
$data = mysql_fetch_array($query_resource);

and echo $data[1];

I get a color name, but it is the same for all cars (not true) and in this example it gives me "yellow".

This leads me to believe that $data is merely pulling all colors and showing me the last record it could find. - Not the goal. There must be an easy way to pull the associated data from the colors table.

I working on finding a place to associate the correct number $onecar[2]; with the correct color name.

I'm use to querying a second table with a value that it receives from the first query.

An example: I query all cars with the above feature, then when each record comes back I take the color_id value and search the colors table for a match - then pull the array from the colors table to match the id with the color name.

While it would be much easier to merely have the color name stored in the cars table, it does not lend itself to easy updating records if say you want all color red listings to be changed to cherry.

Thanks again for all of the help. This has been the best learning experience!

[cameraman]

$query_resource = mysql_query("SELECT * FROM colors WHERE color_id=$color_id",$conn);
$data = mysql_fetch_array($query_resource);

and echo $data[1];

What you're doing there is asking for one color - the one identified by $color_id.
Since you want all of them, you want to "SELECT * FROM colors"
then set up a while loop to get them - just like the feature descriptions.

[jcolgate]

Hmmm, I'm missing something. I figured during the array of the cars table I could pull which color was related, then query the colors table to find a match.

I tried

putting the following within the query of cars... and before it as well...
$query_resource = mysql_query("SELECT * FROM colors",$conn);
$data = mysql_fetch_array($query_resource);
while ($newArray = mysql_fetch_array ($verify_colors_res)) {
$color_name = $newArray[color_name];
}

...without success. I keep getting the first color only.

I know I'm barking up the wrong tree now. Perhaps the Colors query needs to be completely separate and not within any other query.

Thanks again for your time!

JC

[jatar_k]

you have a couple of errors, I am guessing it should be

$query_resource = mysql_query("SELECT * FROM colors",$conn);
while ($newArray = mysql_fetch_array ($query_resource)) {
$color_name = $newArray[color_name];
}

you also don't need $conn in the function unless you have more than one connection open.

I am not sure what you mean by 'within the query for cars', if this means you run this query many times then I wouldn't do that, too slow, too intensive4.

What you could do is pull all the colours at the beginning and build an array with the color_id as the key and the name of the colour as the value, then you could use the color_id from your car query to reference the colour array.

[cameraman]

You could do it either way. I think it would be more efficient to pull all of the color names into an array and get them by the car's color id. That way you have one query for features, one query for colors, and one query to retrieve the cars which match the user's choice.

Doing it as you're suggesting is fine, but results in a whole bunch more queries: the feature query, the matched car query, and one query for each car in the database. 5 or 10 cars and you'd never notice it, but 5,000 cars, or 50,000 cars, you'd probably see it go 'sluggish'.

Here's the implementation of your suggestion:
Inside the car's query after you assign $color_id:
$query_resource = mysql_query("SELECT * FROM colors WHERE color_id=$color_id",$conn);
$data = mysql_fetch_array($query_resource);

then tack it on to the end of this array:
$car_to_display[] = array($cars_name,$feature_id,$data['color_name']); // Notice I used the unexploded one.

If you do a separate query to retrieve all of the colors beforehand (how I'm suggesting):
$query_resource = mysql_query("SELECT * FROM colors",$conn);
while($color = mysql_fetch_array($query_resource))
$colors[$color['color_id']] = $color['color_name'];

Then
$car_to_display[] = array($cars_name,$feature_id,$colors[$color_id]); // Notice I used the unexploded

[jcolgate]

Excellent, now it is coming together. It makes sense to pull data once, and then match it up to your needs. Rather than pulling data each time and using up the server.

Thank you both!
JC