php linking two tables using explode

[nukabolhi]

How can I link two tables using php/mysql. I am confused to query two table. Appreciate if you can figure out and let me know soon.

// customer table
cust_id, impid,
1, 1,2

// sports table
sport id, sport name
1, football
2, basketball

$sql1=mysql_query("SELECT impid FROM customer where c_id=1") or die (mysql_error());
$result1 = mysql_fetch_array($sql1);
echo $exp= $result1['impid']; // get 1, 2 from here 
echo $array=explode(",",$exp); // cannot get the output from explode function, How can I get the sports name from here.

[Bjom]

SELECT fld1, fld2.... FROM tblA INNER JOIN tblB ON tblA.pk = tblB.fk

do use mysqli (see here)

don't use "or die"

and have a looksee here

[Res]

I think the issue is that it's a string containing two values. Where in table one, the values are 1,2. Which refer to two seperate rows in the second table. As such inner join will not quiet work. You might be able to use mysql functions to do the explode for you, but I don't have any knowledge on that. You're along the right lines for doing it in PHP though.

$sql1 = mysql_query("SELECT impid FROM customer where c_id=1") or die (mysql_error());
$result1 = mysql_fetch_array($sql1);
$exp= $result1['impid']; // get 1, 2 from here 
$array=explode(",",$exp); // cannot get the output from explode function, How can I get the sports name from here.

//now you loop over them and get the values for each id
foreach($array as $value){
	$sql2 = mysql_query("SELECT * FROM customer where sport_id='".$value."'") or die (mysql_error());
	//...
}

Hope that sets you on your way.

--edit--
If it's possible there is only one entry, you will want to check for the comma before you explode, otherwise it will cry at you.

if(strstr($exp, ',')){
$array=explode(",",$exp); // cannot get the output from explode function, How 
}else{
//I put it into an array if it's a single value so you can still use the foreach, no
//processing code for a single record and a multiple record.
$array = array($exp);
}

foreach($array as $value){
	$sql2 = mysql_query("SELECT * FROM customer where sport_id='".$value."'") or die (mysql_error());
	//...
}

[nukabolhi]

Thanks, Your code works.

I use while loop to explode and I modified the code the way I want

$sql4=mysql_query("SELECT * FROM customer WHERE c_id=1") or die (mysql_error());

// explode the script 
while ($row4=mysql_fetch_array($sql4))  {
$exp=$act_name=$row4['impid'];
$aname=$row4['sport_name'];
$price=$row4['sport_price'];
$try=explode(",",$exp);
}

if(strstr($try, ',')){
$array=explode(",",$try);
}else{

$array = array($try);
}

// linking the sports id and customer selected sports id 
foreach( $try as $name){
 $sql2 = mysql_query("SELECT * FROM sports where sport_id='".$name."'") or die (mysql_error());
 $result2 = mysql_fetch_array($sql2);
 echo $result2['sport_name']; 
 echo " $". $id[]=$result2['sport_price'] ."<br>"; 
}

// total array value
echo $total = array_sum($id);  

[Res]

I could just be reading too much into it. Currently you have it setup to work properly for one customer. If you are ever in a situation where you are grabbing multiple customers, you would want to be doing your sql2 stuff inside of the loop you are getting the customer data for. Otherwise you're only going to have sport_ids in the array for the last customer you looped over. This code should also work for a single customer, it just allows you to start running multiples without editing the code.

You could further index your $id[] instead with $id[$result[c_id]], to give you a total for each customer in a single array.

In either case if we've got this all settled could you use the 'Thread Tools' at the top and mark this post as resolved? Glad everything is working now!

		$sql4=mysql_query("SELECT * FROM customer WHERE c_id=1") or die (mysql_error());

	// explode the script
	while ($row4=mysql_fetch_array($sql4))  {
		$exp=$act_name=$row4['impid'];
		$aname=$row4['sport_name'];
		$price=$row4['sport_price'];

		if(strstr($exp, ',')){
			$array=explode(",",$try);
		}else{
			$array = array($try);
		}

		// linking the sports id and customer selected sports id
		foreach( $try as $name){
			$sql2 = mysql_query("SELECT * FROM sports where sport_id='".$name."'") or die (mysql_error());
			$result2 = mysql_fetch_array($sql2);
			echo $result2['sport_name'];
			echo " $". $id[]=$result2['sport_price'] ."<br>";
		}

	}

	// total array value
	echo $total = array_sum($id);