Pulling duplicate data from the database

[brob]

Hi I have a table that contains information

 

id | room_id | name | time

1    12          room1  12:00

2    12          room1  16:00

3    7            room2  13:00

4    7            room2  10:00

 

 

How do I pull the information from the table and merge it based on the room_id.

 

So I can get all of the information from room_id and add it into 1 statement?

 

Any help would be great.

 

[simcoweb]

you'd use the 'WHERE' clause in your query:

 

$sql = "SELECT * FROM table_name WHERE room_id='$room_id' ";

 

You would set the value for $room_id by passing the id through the URL and snatching it with the $_GET statement

 

url: http://www.yoursite.com/getstuff.php?room_id=12

 

then, in your script:  $room_id = $_GET['room_id'];

[brob]

this might explain it a bit better

 

$result2=mysql_query("select contact_id,mobile_no from mrbs_tel"); 
while ($getid = mysql_fetch_row($result2))
{
$result=mysql_query("select start_time, end_time, room_id, name, description from mrbs_entry where room_id = $getid[0] and start_time >= '$today_start' and end_time < '$today_end'"); 
while ($i = mysql_fetch_row($result))
{
	if(mysql_num_rows($result) > 1)
	{
		$insert = "insert into mrbs_dupes values (\"\",\"$i[2]\",\"$i[0]\",\"$i[1]\",\"$i[3]\",\"$i[4]\")";
		$inserted = mysql_query($insert);

		$selectdupes = mysql_query("select * from mrbs_dupes where room_id = '$i[2]'");
		$selecteddupes = mysql_fetch_row($selectdupes);

		echo "DUPES - $selecteddupes[1]<br>";
	}
	else
	{
		//Get time of appointment
		$start_time = date("H:i", $i[0]);
		$end_time = date("H:i", $i[1]);

		//Store mobile number of contact
		$tel = str_replace(" ", "", $getid[1]);
		$mobile = substr($tel, 1);
		$mobile_tel = "44$mobile";

		//Trim the text message body to 160 characters and replace spaces / multiple spaces with +
		$trimbody = nicetrim($i[3]);
		$checkbody = str_replace(" ", "+", str_replace("-", "", $trimbody));
		$recheckbody = str_replace("++", "+", $checkbody);
		$finalstripbody = str_replace("++", "+", $recheckbody);
		$body = "Your+schedule+for+tomorrow+is:+$start_time+-+$end_time+$finalstripbody";

		//Add all info into the url to be sent to fastsms
		$url = "http://api.fastsms.co.uk/api/api.php?Username=un&Password=pass&Action=Send&DestinationAddress=$mobile_tel&SourceAddress=add&Body=$body";

		//Send the URL to fastsms to be processed - comment out while testing
		#include $url;

		//List all contacts message was sent to (use only while in development)
		echo "Message was sent to $mobile_tel - $i[2] with the text $body<br>$url<br><br>";
	}
}
}

 

I can't do what you have suggested as the duplicate room entries change on each run of the script. I need to be able to pull this info out on the fly and merge the multiple sets of data in one statement i.e. one for room_id, one for name etc etc any advice?

[JasonLewis]

are you thinking of the GROUP BY function? if see here is a tutorial on it: http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

[brob]

hi group by only aggregates the results and doesn't pull all of the info out.

 

Do you know of a way to do this?