Combining Query Result Rows Into Array

[millsy007]

I have a function that checks for duplicate names, currently it works off one column:

 

$query = "
SELECT   seat
FROM     journey
WHERE    shuttle_id = '$id'
AND    	 seat LIKE '$name%'
";

$qry_result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($qry_result);
if ($num_rows > 0) {   // if it exists, then put all similar names into an array

	while($row = mysql_fetch_array($qry_result))
	{  
		$similar_names[] = $row[seat];

	}
		// check in the similar names array  if your name exists, if so, adds 1 to suffix, then check again until you find your final suffix.
		$suffix = 1;
		while (in_array($name.$suffix, $similar_names))
		$suffix++;
		$name = $name.$suffix;

		$finalname = $name;
	}
else
	{
	//$name is already unique. no suffix needed then.
	$final_name = $name;
	}

 

However I have multiple seats so is there a way that I could check against all the seats so my query would be:

 

$query = "
SELECT   seat1, seat2, seat3
FROM     journey
WHERE    journey.shuttle_id = '$id'
AND    	 seat1 LIKE '$name%'
";

and then somehow combine the result into the array that I check against:

$similar_names[] = $row[seat] //And $row[seat2] And $row[seat3]?

[millsy007]

Could I perhaps use the array_combine:

 

$array1[] = $row[seat1];
$array2[] = $row[seat2];
$array3[] = $row[seat3];
$similar_names[] = array_merge($array1, $array2, $array3);

 

This way would all of the columns be merged into one array I could then use?

[sasa]

just do

		while($row = mysql_fetch_array($qry_result))
	{  
		$similar_names[] = $row['seat1'];
		$similar_names[] = $row['seat2'];
		$similar_names[] = $row['seat3'];
	}

[millsy007]

I do

 

$query = "
SELECT   seat1, seat2, seat3, seat4, seat5, seat6, seat7, seat8
FROM     journey
WHERE    journey.shuttle_id = '$id'
AND    	 seat1 LIKE '$name%'
";

$qry_result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($qry_result);
if ($num_rows > 0) {   // if it exists, then put all similar names into an array

	while($row = mysql_fetch_array($qry_result))
	{  
$similar_names[] = $row[seat1];
$similar_names[] = $row[seat2];
$similar_names[] = $row[seat3];
$similar_names[] = $row[seat4];
$similar_names[] = $row[seat5];
$similar_names[] = $row[seat6];
$similar_names[] = $row[seat7];
$similar_names[] = $row[seat8];

	}

 

But it is still only checking the data from the:

$similar_names[] = $row[seat1];

Part of the code

[kickstart]

Hi

 

Think the basic problem is a database design issue. Having a seperate column for each seat is very much against basic design ideas. You really should split the different seats off onto a seperate table.

 

However if you do have a fixed number of columns then

 

$query = "
SELECT   seat1
FROM     journey
WHERE    journey.shuttle_id = '$id'
AND    	 seat1 LIKE '$name%'
UNION ALL
SELECT   seat2
FROM     journey
WHERE    journey.shuttle_id = '$id'
AND    	 seat2 LIKE '$name%'
UNION ALL
SELECT   seat3
FROM     journey
WHERE    journey.shuttle_id = '$id'
AND    	 seat3 LIKE '$name%'
";

 

You could add a few more SELECT statements to this if you have more columns.

 

All the best

 

Keith