|
PHP - How To Check The Records In Mysql If It Already Updated
Hi guys,
I need your help. I am checking on a database as I want to see if the records have been updated or not. Code: [Select] <?php session_start(); define('DB_HOST', 'localhost'); define('DB_USER', 'mydbuser'); define('DB_PASSWORD', 'mydbpass'); define('DB_DATABASE', 'mydbtable'); $errmsg_arr = array(); $errflag = false; $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } function clean($var){ return mysql_real_escape_string(strip_tags($var)); } $username = clean($_GET['user']); $password = clean($_GET['pass']); $test = clean($_GET['test']); $public = clean($_GET['public']); if (isset($_GET['user']) && (isset($_GET['pass']))) { if($username == '' || $password == '') { $errmsg_arr[] = 'username or password are missing'; $errflag = true; } } elseif (isset($_GET['user']) || (isset($_GET['test'])) || (isset($_GET['public']))) { if($username == '' || $test == '' || $public == '') { $errmsg_arr[] = 'user or others are missing'; $errflag = true; } } if($errflag) { $_SESSION['ERRMSG_ARR'] = $errmsg_arr; echo implode('<br />',$errmsg_arr); } else { $qry="SELECT * FROM members WHERE username='$username' AND passwd='$password'"; $result=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error()); if ($username && $password) { if(mysql_num_rows($result) > 0) { $qrytable1="SELECT images, id, test, links, Public FROM user_channel_list WHERE username='$username'"; $result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error()); while ($row = mysql_fetch_array($result1)) { echo "<p id='test'>"; echo $row['test'] . "</p>"; echo '<p id="images"> <a href="images.php?test=test&id='.$row['id'].'">Images</a></td> | <a href="http://' . $row["links"] . '">Link</a> </td> | <a href="delete.php?test=test&id='.$row['id'].'">Delete</a> </td> | <span id="test">'.$row['Public'].'</td>'; } } else { echo "user not found"; } } elseif($username && $test && $public) { $qry="SELECT * FROM members WHERE username='$username'"; $result1=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error()); if(mysql_num_rows($result1) > 0) { $qrytable1="SELECT Public FROM user_channel_list WHERE username='$username' && test='$test'"; $result2=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error()); if(mysql_num_rows($result2) > 0) { $row = mysql_fetch_row($result2); mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'"); echo "update!"; } else { echo "already updated!"; } } else { echo "user not found"; } } } ?> When I run debug the code on my php, if i input the data in a url bar while the records are the same as the data that I enter in the url, i should get the print out on my php "already updated", but I keep getting "update!". Do you know how i can check on mysql database to see if the records have been updated or not?? Similar TutorialsMy script is finally working as intended, but I want to add some additional data results. I am trying to resolve how I can display a count of ONLY the records updated by my query: // START :: Query to replace matches mysql_query("UPDATE orig_codes_1a AS a JOIN old_and_new_codes_1a AS b ON concat(a.orig_code_1, a.orig_code_2) = concat(b.old_code_1, b.old_code_2) SET a.orig_code_1 = b.new_code_1, a.orig_code_2 = b.new_code_2") or die(mysql_error()); // END :: Query to replace matches In this query I count ALL records selection: // START :: Create query to be displayed as final results of original codes table. $result = mysql_query("SELECT * FROM orig_codes_1a") or die(mysql_error()); I want to display a count on this part of the query: ....concat(a.orig_code_1, a.orig_code_2) = concat(b.old_code_1, b.old_code_2).... I'm creating a game where 2 people, on separate computers can battle each other. I'm doing it turn-based, like Final Fantasy but I came across a problem, how do I check if the other person has ended their turn? I was thinking that I would have an area in my database that would be true/false if it was their turn. So if the first person ends their turn, their 'turn' becomes false in the database, but how, on the other users computer, do I constantly check if the 'turn' has become false so that they may now play without having to keep updating the browser. Or is their a better way to do something like this? I have been using an inventory application built on PHP/MySQL. Since this morning I could submit the data and they were perfectly reflected on the MySQL Table. However, for a few hours I cannot save the submitted data to the table and it doesn't show any error message. Please note no change have been made since it was successfully running. The developer of this application is not available right now.
PLEASR HELP I AM A NOVICE IN PHP/MYSQL.
I am creating a stream page that updates automatically without the page reloading by reloading a div on the page using a timer. However, I only want it to run the update and fade in/fade out when there is a new status in the table. I tried doing it through PHP getting the number of rows in the table on the page itself and then checking the number of rows on an action page, however, the number of rows didnt update when the div was reloaded. I then tried doing it using jquery/javscript but had the same problem. How can I acheive this? Thanks in advance I have a customer registration form up and running, the form allows customers to complete the forms and the data is then inserted into mysql db table via php form. I want to prevent duplicates records from already existing customers from creating a new records instead I want the customers to update their existing records in the backend. That is allow them to complete the form as normal, once they hit the submit button, the code should check if the customer first name and last name is already in the database if yes then it should update their records and if not it should insert new record to the table. I do not want to alert the customer that their records already exist. I do not know where to start. Please help Thank you so much in advance for your help. Hello all, I am new to php coding and have a couple of problems with editing records in my database! I have two files below one test.php and edit.php. In the test.php the code outputs the records into a table. The problem is with the edit link as when it is selected I wish to be able to edit a record by a form, which is on edit.php. I am trying bring up the movie's information on the form to be edited. Currently on the form i get; Quote Movie: movie Gen Genre Year: year Any ideas how I can edit the record and then return to the test.php page? Code: [Select] test.php <html> <body style="background-color:#669999;"> <table width="490"border=0><tr> <td colspan="2" style="background-color:#FFA500;"> <div id="header" <h3 style="color:black">This is my first web-page! Below is a database of some of my favourite movies! </h3> </td> </tr> <?php //connecting to server $con = mysql_connect("localhost","root","NYOXAkly"); if (!$con) { die('could not connect: ' . mysql_error()); } //selecting movie database mysql_select_db("my_mov",$con); //Check if add button is active, start this if(isset($_REQUEST['add'])) { echo "<meta http-equiv=\"refresh\"content=\"0;URL=form.php\">"; } $result = mysql_query("SELECT * FROM Films ORDER BY filmID"); ?> <!-------------------------------creating table------------------------------------------------------------------------------------> <table width="490" border="0" cellspacing="1" cellpadding="0"> <tr> <td> <form name="test1" method="post" action="test.php"> <table width="490" border="10" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"><tr> <td bgcolor="#FFFFFF"></td> <td align="center" colspan="6" bgcolor="#FFFFFF">Movie Database</td></tr> <td align="center" bgcolor="#FFFFFF">filmID</td> <td align="center" bgcolor="#FFFFFF">Movie</td> <td align="center" bgcolor="#FFFFFF">Genre</td> <td align="center" bgcolor="#FFFFFF">Year</td> <td align="center" bgcolor="#FFFFFF">Edit</td> <td align="center" bgcolor="#FFFFFF">Delete</td> </tr> <?php while($rows=mysql_fetch_array($result)) { ?> <tr> </td> <td bgcolor="#FFFFFF"><? echo $rows['filmID']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['movie']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['genre']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['year']; ?></td> <td bgcolor="#FFFFFF"> <a href="edit.php?filmID=<?php echo 'filmID';?>">Edit</a> <td bgcolor="#FFFFFF"> <a href="delete.php?filmID=<?php echo 'filmID';?>">Delete</a> </td> </tr> <?php } echo print_r(error_get_last()); mysql_close(); ?> <!--add button--> <tr> <td colspan="15" align="left" bgcolor="#FFFFFF"> <input name='add' type="submit" filmID="add" value="Add A New Record" action="form.php?"> </td> </tr> </table> </form> <br/> By C.M.D.W <br/> <?php echo date("Y/m/d") . "<br />"; ?> </body> </html> Code: [Select] edit.php <html> <body style="background-color:#669999;"> <!------------------Creates a form ----------------------> <br /> <form action="" method="post"> <fieldset> <legend>Enter your movies into database here!</legend> Movie: <input type ="text" name="movie" value="<?php echo 'movie';?>"> <br /> Gen <input type ="text" name="genre" value="<?php echo 'genre';?>"/> <br /> Year: <input type ="text" name="year" value="<?php echo 'year';?>"/> <br /> <input type="submit" name="name" value="Submit" /> </fieldset> </form> <?php //connecting to server $con = mysql_connect("localhost","root","NYOXAkly"); if (!$con) { die('could not connect: ' . mysql_error()); } //selecting movie database mysql_select_db("my_mov",$con); if (isset($_POST['submit'])) { // confirm that the 'id' value is a valid integer if (is_numeric($_POST['filmID'])) // get form data $filmID = $_POST['filmID']; $movie = mysql_real_escape_string(htmlspecialchars($_POST['movie'])); $genre = mysql_real_escape_string(htmlspecialchars($_POST['genre'])); $year = mysql_real_escape_string(htmlspecialchars($_POST['year'])); // check that fields are filled in if ($movie == '' || $genre == '' || $year == '') { // generate error message $error = 'ERROR: Please fill in all required fields!'; } else { // save the data to the database } mysql_query("UPDATE players SET movie='$movie', genre='$genre', year='$year' WHERE filmID='$filmID'") or die(mysql_error()); // once saved, redirect back to the view page header("Location: test.php"); } } if (isset($_GET['filmID']) && is_numeric($_GET['filmID']) && $_GET['filmID'] > 0) { // query db $id = $_GET['filmID']; $result = mysql_query("SELECT * FROM Films WHERE filmID=$FilmID") or die(mysql_error()); $row = mysql_fetch_array($result); // check that the 'id' matches up with a row in the databse if($row) { // get data from db $movie = $row['movie']; $genre = $row['genre']; $year = $row['year']; }} ?> <br/> <br/> <a href="test.php">Return To Home Page</a> <br/> <br/> By C.M.D.W <br/> <?php echo date("Y/m/d") . "<br />"; ?> </body> </html> Thanks Chris Hi guys, I am trying to UPDATE some records on a mySQL database but can't seem to find out why it is not working. This is my code. Code: [Select] <?php $latitude = $_POST['lat_location']; $longitude = $_POST['long_location']; $unique_ID = $_POST['unique_ID']; include('connect2.php'); $query = mysql_query("SELECT * FROM user_location WHERE unit = '$unique_ID'"); $numrows = mysql_num_rows($query); if ($numrows == 1) { $query2="UPDATE user_location SET lat = '$latitude', long = '$longitude' WHERE unit = '$unique_ID'"; mysql_query($query2); $test = "matches"; } else { mysql_query("INSERT INTO user_location VALUES ('','$unique_ID','$latitude','$longitude')"); $test = "not match"; } echo $test . "<br />"; echo $numrows; ?> The script receives the data via the POST method and assigns it to variables. Then I query the database for one of those variables and check how many results are found. If 0 results are found then a new record is created on the database, but if there is 1 record found then the record that is found has to be UPDATED. When the result is 0 the scripts creates the new record fine, but if the result is 1 it doesn't update. I just can't figure out why. Any help will be greatly appreciated. Thanks in advanced, I have a problem . I 've been trying for a long time to make an update for php mysql to change the data. but every time I do not manage to make it work with a form. but it works if I only if I put this ($ sql = "UPDATE users SET username = 'value' WHERE id = 10 " ; ) so it only works when I put the value of the id. but I want in an html form to indicate what I want to change and what id goes. but I have tried so long that I do not feel like I so want someone help me. make the same database and same as my records and make the code and test it if it works show me please my database name : web test my table called : users my records are called : id int ( 11) AUTO_INNCREMENT username , varchar ( 255 ) password , varchar ( 255 ) first_name , varchar ( 255 ) last_name , varchar ( 255 ) email, varchar ( 255 ) Age, int ( 11) Look, my update.php is like this now <?php $servername = "localhost"; $username = "root"; $password = "....."; $dbname = "webtest"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "UPDATE users SET password='cotton candy' WHERE id=10"; if ($conn->query($sql) === TRUE) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn->error; } $conn->close(); ?> but now i have still have to go into the php file to change the valeu or the id but i looked on site and youtube how to put it in a simple html form but it still does not work. i want it in a html from. I want that when I enter the ID that the data of the user appears and that I can change any valeu separately. I'm sorry to be back so soon, but I'm up against another mystery. I'm using the code below to enter a bunch of css data from a spreadsheet into a mysql table. I think the data file is OK. The array created by the script checks out with print_r. (There are many more records than shown. I truncated it to save space.) The problem is that I get this error regarding my sql statement, not the data or anything else: Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'check, name, phone, email, entry_fee, print_fee, image_name, description, med...' at line 1 in /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php:242 Stack trace: #0 /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php(242): PDO->prepare('INSERT INTO tbl...') #1 {main} thrown in /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php on line 242 I've typed it in a dozen times to make sure there are no errors and keep getting the same error. I tried running a test file and gradually increasing the number of placeholders and at some point I always end up getting the same error, I can delete the most recent addition and it works again. Then I can add another placeholder exactly as before and it works the second time. It feels like a ghost in the machine. Any idea what I am doing wrong? An I typing something I don't see?
<?php
require '__classes/Db.php';
$csvData = '1,FALSE,Carol Lettko,,,TRUE,FALSE,Carol_Lettko-DSC_3022.jpg,Baby Herons/Brickyard,photo,,,
,,,925-285-0320,cjl164@aol.com,,,Carol_Lettko-DSC_0164.JPG,Heron/Brickyard,photo,,,
,,,,,,,Carol_Lettko-IMG_5723.jpg,Kayaker/Brickyard,photo,,,
,,,,,,,,,,,,
2,FALSE,Louise Williams,,,TRUE,FALSE,Louise_Williams-BirdsOfAFeatherAOPR.jpg,Alligator with Words,Book Excerpt,,,
,,,510-232-9547,lkw@louisekwilliams.com,,,Louise_Williams-Hope-TheFairyChickenAOPR.jpg,Hope The Fairy Chicken,,,,
,,,The d exatrfrfvct/.*tygrvurr,,,,,,,,,
,,,,,,,,,,,,
3,TRUE,Dorothy Leeland,,lelanddorothy@gmail.com,TRUE,FALSE,DJ_Lee-bridge at dusk 700px width.jpg,Bridge,photo,,,
,,,,,,,DJ_Lee-friends 700px width.jpg,Friends,photo,,,
,,,,,,,DJ_Lee-hybiscus 700 px wide.jpg,Hibiscus,photo,,,
,,,,,,,,,,,,
4,FALSE,Rita Gardner,,,TRUE,FALSE,Rita_Gardner-Explosion - Gardner photo.JPG,Explosion,photo,,,
,,,,tropicrita@msn.com,,,Rita_Gardner-Ferry Point tables and chair - Gardner.JPG,Ferry Point Tables,photo,, ,
,,,,,,,Rita_Gardner-Forks - Gardner photo.JPG,Forks,photo,,,
,,,,,,,,,,,,
';
$lines = explode(PHP_EOL, $csvData);
$array1 = array();
foreach ($lines as $line) {
$array1[] = str_getcsv($line);
}
$stmt = $pdo->prepare("INSERT INTO tbl_person_data (number, check, name, phone, email, entry_fee, print_fee, image_name, description, medium, select, orient, site) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
foreach ($array1 as $row)
{
$stmt->execute('$row');
}
echo '<pre>';
print_r($array1);
echo '</pre>';
?>
Hello, This is driving me crazy, as I cannot get it working. I have a database that has a table called 'categories'. I need to retrieve all categories with category_id != '1,2,3'. For this, I can this query: Code: [Select] $q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3)"; The query executes fine. I need this categories retrieved, to be passed to a Smarty Template Engine powered page. I have: Code: [Select] $q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3)"; $res = mysql_query($q); while($row=mysql_fetch_array($res)) { Assign('categories',$row); } But when retrieving the array in the Smarty page, with a loop, I only see the last item of the array (the last category). If I go back to the while loop, and add a Code: [Select] print_r($row);, I see two arrays (I have two categories). But the question is: How can I work with those two categories, as I need to use them individually outside the while loop? Any help is appreciated. Best Regards, Richi Hi,I'm hoping someone can help me:). When I add foreign keys to the database in MySQL I CAN change the primary key field and the corresponding indexed field will automatically change giving me the same value which is exactly what i want. The issue is when I try to insert the values using my php code i get the 'cannot update child error'. I have not selected a value for the unique_id field as I want it to inherit the value from the primary key. Here's my code as simplified as I could do it. <?php //this calls the class productupload and instantiates the function insert_form to insert values into the db. $product = new productUpload(); $product->insert_form();?> //This calls the insert_form function and inserts info into tables <?php class productUpload extends DatabaseObject{ protected static $table_name="itm_details"; protected static $db_fields =array('id','unique_id','itm_cat','itm_make','itm_model','itm_desc','itm_cond','itm_date_from','itm_date_to','itm_add_date'); public $id; public $unique_id; public $itm_cat; public $itm_make; public $itm_model; public $itm_desc; public $itm_cond; public $itm_date_from; public $itm_date_to; public $itm_add_date; Heres the function insert_form which grabs the form submitted data. Note I have not defined a value for $unique_id public function insert_form(){ global $database; //set the object attributes to the form the parameters if(isset($_POST['submit'])) { $result = array( $this->itm_cat =(!empty($_POST['itm_cat'])) ? trim($_POST['itm_cat']) : NULL, $this->itm_make =(!empty($_POST['itm_make'])) ? trim($_POST['itm_make']) : NULL, $this->itm_model = (!empty($_POST['itm_model'])) ? trim($_POST['itm_model']) : NULL, $this->itm_desc =(!empty($_POST['itm_desc'])) ? trim($_POST['itm_desc']) : NULL, $this->itm_cond =(!empty($_POST['itm_cond'])) ? trim($_POST['itm_cond']) : NULL, $this->itm_date_from =(!empty($_POST['itm_date_from'])) ? trim($_POST['itm_date_from']) : NULL, $this->itm_date_to =(!empty($_POST['itm_date_to'])) ? trim($_POST['itm_date_to']) : NULL, $this->itm_add_date = date("Y-m-d H:m:s")); //check date is numeric //if(is_numeric($_POST['itm_date_from'])) { //$this->itm_date_from = $_POST['itm_date_from']; //} //check date is numeric //if(is_numeric($_POST['itm_date_to'])) { //$this->itm_date_to = $_POST['itm_date_to']; //} if($result){ $result = $this->create(); } } } //here's the create function referred to above which sanitises the posted data and inserts it into the db. public function create() { global $database; $attributes = $this->sanitised_attributes(); $sql = "INSERT INTO ".self::$table_name."("; $sql .= join(", ", array_keys($attributes)); $sql .= ") VALUES ('"; $sql .= join("', '", array_values($attributes)); $sql .= "')"; if($database->query($sql)) { $this->id = $database->insert_id(); return true; } else { return false; } } Code: [Select] <?php require "db/config.php"; $fname = $_POST['fname']; $lname = $_POST['lname']; $country = $_POST['country']; $state = $_POST['state']; $city = $_POST['city']; $zcode = $_POST['zcode']; $address = $_POST['address']; $ppemail = $_POST['ppemail']; $pnumber = $_POST['pnumber']; $cemail = $_POST['cemail']; $url = $_POST['url']; $price = "$5.00"; $query = "INSERT INTO custpackage1000( id, FirstName, LastName, Country, State, City, ZipCode, Address, PayPalEmail, PhoneNumber, PrimaryEmail, WebsiteURL) VALUES ( '1', '$fname', '$lname', '$country', '$state', '$city', '$zcode', '$ppemail', '$pnumber', '$cemail', '$url')"; mysql_connect($host, $user, $pass) or die("<br /><br /><h1>Fatal error. Please contact support if this persists.</h1>"); mysql_select_db($dbname); mysql_query($query) or die ("could not open db".mysql_error()); sleep(2); ?> Why won't the code insert into my database upon submission of data? What am I doing wrong? Hi, I'm new to the forum so this could go in the MySQL section but I'm not sure. I am trying to make a page that will list all records from a column in HTML table and have a delete button to remove a specific record. I have got to the part where I have listed the records in a table. Note: Only records from a specific column ('links') are printed. Here is the code: Code: [Select] $con = mysql_connect("localhost","***","***"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("***", $con); $result = mysql_query("SELECT * FROM main"); echo "<table border='1'> <tr> <th>Current links</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['links'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); How would I go about adding a delete button next to each record to delete that specific record? Thanks for any help. How do you do this? Thanks in advance. I wonder whether someone can help me please. I've found http://www.plus2net.com/php_tutorial/ajax-listbox.php tutorial to create a drop down menu using mySQL table data, which, in turn returns a list of results on the page. Following this tutorial I've put together the tables in my database and the required scripts as shown in the tutorial with the one exception, the "z_db.php" file, which I've assumed to be: Code: [Select] <?php mysql_connect("host", "user", "password")or die(mysql_error()); mysql_select_db("database"); ?> The problem I have, is that when I try and run this, I receive the following error: Quote Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /homepages/2/d333603417/htdocs/development/catsearch.php on line 91 which is this line in the search form: echo "</head><body onload="ajaxFunction()";>";. I must admit I've guessed as to the structure of the 'z_db.php' file should look like because this is not shown so perhaps this is the problem. I just wondered wether someone could perhaps take a look at this please and let me know where I've gone wrong. Many thanks and kind regards I want to pull some records plus unique key from one mysql table and then use them as questions in a form/questionnaire. The post form will have six radio buttons to the right of the fields pulled with the key posted but hidden and will wirte to another table. I guess this is a templating question- as I've successfully retrieved the table 1 records via a query - I just can't seem to use them in the form. Below is the code I'm using to view table "PCM1" <?php // query.php require_once 'login.php'; $db_server = mysql_connect($db_hostname, $db_username, $db_password); if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database) or die("Unable to select database: " . mysql_error()); $query = "SELECT * FROM pcm1"; $result = mysql_query($query); if (!$result) die ("Database access failed: " . mysql_error()); $rows = mysql_num_rows($result); for ($j = 0 ; $j < $rows ; ++$j) { echo 'RID: ' . mysql_result($result,$j,'mcde') ; echo 'Statement: ' . mysql_result($result,$j,'oppclub') . '<br />'; ; } Hi, I need to compare records in a table based on the 'datetime' field, like if the difference between time is less than 15min. need to combine the records(rows) as a single resultant row until the time difference between them is less than 15min and when a record's time difference is >15min that should return as another row and so on. Please find following sample table and the required output format Sample Table: Code: [Select] ID NAME DATETIME 1 aaa 2011-10-10 06:30:00 2 bbb 2011-10-10 06:33:00 3 ccc 2011-10-10 06:38:00 4 ddd 2011-10-10 06:40:00 5 eee 2011-10-10 07:10:00 6 ffff 2011-10-10 07:14:00 7 sss 2011-10-10 08:16:00 8 jjj 2011-10-10 08:26:00 9 kkk 2011-10-10 08:28:00 10 mm 2011-10-10 09:46:00 11 ppp 2011-10-10 09:49:00 12 qqq 2011-10-10 09:52:00 Output Needed : Code: [Select] IDs START DATETIME END DATETIME 1,2,3,4 2011-10-10 06:30:00 2011-10-10 06:40:00 5,6 2011-10-10 07:10:00 2011-10-10 07:14:00 7,8,9 2011-10-10 08:16:00 2011-10-10 08:28:00 10,11,12 2011-10-10 09:46:00 2011-10-10 09:52:00 You can see 1st row in the output table having IDs 1,2,3,4 coz the time difference between them is less than 15minutes (it doesn't means time difference between start and end; it is actually the time difference between current record and previous one ; ie; ID-1 and ID-2 have difference less than 15min and ID-2 & ID-3 have difference less than 15min and ID-3 & ID-4 have difference less than 15min, so those 4 records combine together as a single row also shows their start time and end time. Then you can see diff between ID-4 and ID-5 is greater than 15 min so it should display as new row, and so on) How can I acheive above Output with mysql query ?? Please help.. I am having trouble showing reports for a given date range. Currently if I specify something like 11/03/2010 to 11/05/2010 I get results for all years within that month and day such as I may get results for 11/03/2008 11/03/2009 11/03/2010 11/04/2008 11/04/2009 11/04/2010 11/05/2008 11/05/2009 11/05/2010. I am using the following code $result = mysql_query("SELECT * FROM report WHERE date>='$date_begin' and date<='$date_end' ORDER BY 'date'",$db); I use the following format in my date feild mm/dd/yyyy |
|||||||