|
PHP - Reorder Mysql Rows
I have a HTML dropdown list which is populated from a MySQL table. I use an auto-incremend ID field. I have also got a sort_order field.
I would like to be able to specify the exact order that entries should appear in the dropdown list. Specifically I need some functionality that does the following two things: 1) If I change the sort order of a record to for example "4" then the existing record with sort_order 4 becomes 5, sort_order 5 becomes 6 and so on. 2) If I change the sort order of a record to for example "4" and there is currently no record with sort_order 4, then no other records will be updated. Similar Tutorialsim wondering how to get php to read the latest row (instead of show them all) and when another one comes it replaces it once the page refreshes I have a MySQL query that returns rows containing date_add column like below:
DATE(date_add) 2014-01-07 2014-01-07 2014-01-07 2014-01-07 2014-01-08 2014-01-15 2014-01-20 2014-01-20 2014-01-20 2014-01-26 2014-01-28 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-16 2014-02-18 2014-02-18 2014-03-08 2014-03-08How can I have pagination based on dates and show rows with the same date in a page and not just based on predefined limit number? First off I know this will be a problem with my code but I honestly have looked at it for too long. If anyone can shed a bit of simple light on it that would be great. $count should return about 8 or 9 or so but just returns 1 - I can only print out the number 1 if I do a direct echo of the mysql_num_rows() instead of putting it into the $count variable. Its really confusing, the database is all set up correctly as earlier on in the page I get all the information from the database and print out a table with it. There is something wrong here! Problem only occurs when $count = mysql_num_rows($result1); is written. Code: [Select] //Following user presses button with name applyChanges. if(isset($_POST['applyChanges'])) { $result1 = mysql_query("SELECT * FROM music") or die ("Could not get result1"); $count = mysql_num_rows($result1) or die ("Could not count rows"); //Changed this to an echo without $count and prints a 1. - Should be greater than 8 echo "$count"; for($i=0; $i<$count; $i++) { $query2 = "UPDATE music SET name='$name[$i]' WHERE path='$path[$i]'"; $result2 = mysql_query($query2); } } Thanks, Matt Ok I am trying to delete some rows from a database. I have done this before with success so I thought I could use the same method just modify it a little and it would work. Man was I wrong. Any help on this is much appreciated below is my code. Code for the Query to get the data from the database and for displaying the results in a table: Code: [Select] //GET DATA $sql = "SELECT * FROM `".$tblname."` ORDER BY $orderby $sort LIMIT $startrow,$limit"; $result = mysql_query($sql) or die(mysql_error()); $result2 = mysql_query($sql); //START TABLE AND TABLE HEADER echo "<form name='form1' method='post' action=''><table style='font-size:9.5px;'>\n<tr><th>Delete</th>"; $array = mysql_fetch_assoc($result); foreach ($array as $key=>$value) { if($config['nicefields']){ $field = str_replace("_"," ",$key); $field = ucwords($field); } $field = columnSortArrows($key,$field,$orderby,$sort); echo "<th>" . $field . "</th>\n"; } echo "</tr>\n"; //reset result pointer mysql_data_seek($result,0); //start first row style $tr_class = "class='odd'"; //LOOP TABLE ROWS while($row = mysql_fetch_assoc($result)){ echo "<tr ".$tr_class.">\n<td><a href='remove_rec.php?id=". $rows['ID'] . "'><p>Delete</p></a>"; echo "</td>"; foreach ($row as $field=>$value) { echo "<td>" . $value . "</td>\n"; } echo "</tr>\n"; //switch row style if($tr_class == "class='odd'"){ $tr_class = "class='even'"; }else{ $tr_class = "class='odd'"; } } //END TABLE echo "</table>\n</form>"; Here is the code that should delete the row: Code: [Select] $tblname = 'tablename'; // get value of id that sent from address bar $id=$_GET['ID']; // Delete data in mysql from row that has this id $sql="DELETE FROM $tblname WHERE ID='$id'"; $result=mysql_query($sql); // if successfully deleted if($result){ echo "Deleted Successfully"; echo "<BR>"; echo "<a href='leads3.php'>Back to Results</a>"; } else { echo "ERROR"; } When I click on the link to delete a record it redirects me to the appropriate page with the "Deleted Successfully" message but when I go to view the results the row was not deleted. Any help on this would, again, be greatly appreciated. I'm trying to pull user referral data from my database. This is what I have so far. <table cellpadding="0" cellspacing="0" style="border:1px #000000 solid;" width="68%"> <tr> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> User </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> Date </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> Visits </b></font></td> </tr> <? $lole=$_COOKIE["usNick"]; $tabla = mysql_query("SELECT * FROM tb_users where username='$lole' ORDER BY id ASC"); while ($row = mysql_fetch_array($tabla)) { echo "<tr><td><font size=\"2\" face=\"verdana\">"; echo $row["username"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["email"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["joindate"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["visits"]; echo "</font></td></tr>"; } echo "</table>"; ?> <script type="text/javascript"> initSortTable('myTable',Array('S','N','S','N','S')); </script> I just found out my user ID is missing from database.. Here's the error: Code: [Select] Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/stayway1/public_html/www.one-mafia.com/eng/inboxread.php on line 99 Here's lines 90-110: (99 in bold) Code: [Select] if (strip_tags($_POST['Accept_OC'])){ $oc_id=strip_tags($_POST['oc_id']); if (strip_tags($_POST['place']) == "we"){ $use="we_inv"; $a="we"; $query= "SELECT * FROM oc WHERE we_inv='$username' AND id='$oc_id'"; }elseif (strip_tags($_POST['place']) == "ee"){ $use="ee_inv"; $a="ee"; $query= "SELECT * FROM oc WHERE ee_inv='$username' AND id='$oc_id'"; }elseif (strip_tags($_POST['place']) == "driver"){ $use="driver_inv"; $a="driver"; $query= "SELECT * FROM oc WHERE driver_inv='$username' AND id='$oc_id'"; } $round=mysql_query($query); [b]$check=mysql_num_rows($round);[/b] if ($check != "0"){ echo "You are now in that OC"; mysql_query("UPDATE `oc` SET `$a`='$username' WHERE `id`='$oc_id'"); mysql_query("UPDATE `users` SET `oc`='1' WHERE `username`='$username'"); } } if (strip_tags($_POST['Yes_street'])){ $race_id=strip_tags($_POST['race_id']); if ($fetch->street != "0"){ echo "Your in a race."; }elseif ($fetch->street == "0"){ if ($fetch->last_race >= time()){ echo "You cant do anouther race yet."; }elseif ($fetch->last_race < time()){ Now he sayes other fold: Wrong parameter count for mysql_num_rows() in /home/a5078111/public_html/registerpage.html on line 146 How would I make a script that would delete any row in a MySQL table that was more than 7 days old? I have retrieved 5 values from a database table... The table is such as-> tmeta_id team_id 1 1 2 2 3 3 4 4 5 5 Code: [Select] Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 4 [4] => 5 ) .. and this array I have named $nonrandom_teamno. using shuffle() I have rearranged the array, the new array being $random_teamno, which is such as the following-> Code: [Select] Array ( [0] => 3 [1] => 4 [2] => 5 [3] => 2 [4] => 1 )I am having real problems reinserting this into the SQL tables... I have been trying the foreach loop below... Code: [Select] for($i = 1; $i < (count($nonrandom_teamno)+1); $i++) { $db->query("UPDATE ancl_teammeta SET team_id ='$random_teamno[$i]' WHERE tmeta_id='$nonrandom_teamno[$i]'"); }It puts numbers back in the database, but not random ones. Please help me with the correct query I need!! Thank you.. So I have an array of IDs that I would only like to select from a database table instead of everything in the table. How might I go about doing that? I have a table with several fields, one of which is an amount of days after which the row will be deleted. Every time the script is run, I want to check for old entries and delete them. How would I do this? How can i select the last 3 rows in a table and order it by ASC. When i use DESC it displays the results in the wrong order. $query24 = mysql_query("SELECT * FROM notifications WHERE to_id='$session' AND state='1' ORDER BY id DESC LIMIT 3 "); month a0 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16 a17 a18 a19 a20 a21 a22 a23 a24 a25 a26 a27 a28 a29 a30 a31 1 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 [20] [21] 22 23 24 25 [26] 27 28 29 30 31 2 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 3 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 4 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 5 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 6 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 7 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 8 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 9 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 10 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 11 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 12 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 ; i need help .. with calculating how many days are between example: january = 1 ; day =23 to feb =2; day 23 in php ... how can i do that I was just wondering when you run a mysql query what the easiest way to find out the number of rows returned. Just so I can do a number of results found on a search. Thanks. I am trying to update multiple rows in a mysql db through php but I am getting an error message in this line: $size = count($_POST['uid']); My db field names are uid (which autoincrements and does not need to be updated), fname, lname and email fields. Here is a link for the project I need to develophttp://users.cis.fiu.edu/~vagelis/classes/COP5725/project.htm Here is the content for the first page: Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head> <style type="text/css"> body { margin:50px 0px; padding:0px; text-align:center; font:13px Tahoma,Geneva,sans-serif } #content { width:1000px; margin:0px auto; text-align:left; padding:15px; border:1px dashed #333; background-color:#eee; } </style> <link rel="icon" href="http://www.mysite.com/favicon.ico" type="image/vnd.microsoft.icon" /> </head> <body> <div id='content'><h3><center>Update Information</center></h3> <?php //create a connection $connection = mysql_connect("localhost","root","r2d2c3po"); //test the connection if (!$connection) { die("Database connection failed: " . mysql_error()); } //select database to use mysql_select_db("music_social_network", $connection); //setup query $sql = "SELECT * FROM `user` ORDER BY uid"; //get results from query or die $result = mysql_query($sql, $connection) or die(mysql_error()); //fetch rows from query $rows = mysql_fetch_assoc($result); ?> <?php // find out how many records there are to update $size = count($_POST['uid']); // start a counter in order to number the input fields for each record $i = 0; print "<table width='100%' border='0' cellspacing='1' cellpadding='0'><tr><td>"; // open a form print "<form name='namestoupdate' method='post' action='update.php'> <table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> <td align='center'><strong>User ID</strong></td> <td align='center'><strong>First Name</strong></td> <td align='center'><strong>Last Name</strong></td> <td align='center'><strong>Email</strong></td> </tr>\n"; // start a loop to print all of the users with their information // the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left while ($Update = mysql_fetch_array($result)) { print "</tr>\n"; // assuming you have three important columns (the index (id), the course name (course), and the book info (bookinfo)) // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) print "<td align='center'><p>{$Update['uid']}</p></td>\n"; print "<td align='center'><input type='text' fname='fname[$i]' value='{$Update['fname']}' /></td>"; print "<td align='center'><input type='text' size='40' lname='lname[$i]' value='{$Update['lname']}' /></td>\n"; print "<td align='center'><input type='text' size='40' email='email[$i]' value='{$Update['email']}' /></td>\n"; print "</tr>\n"; // add 1 to the count, close the loop, close the form, and the mysql connection ++$i; } print "<tr> <td colspan='4' align='center'><input type='submit' value='submit' />"; print "</td> </tr> </table> </td> </tr> </form> </table>"; mysql_close($connection); ?><br /><br /><div></body> </html> here is the content for the update.php page: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head> <style type="text/css"> body { margin:50px 0px; padding:0px; text-align:center; font:13px Tahoma,Geneva,sans-serif } #content { width:1000px; margin:0px auto; text-align:left; padding:15px; border:1px dashed #333; background-color:#eee; } </style> <link rel="icon" href="http://www.mysite.com/favicon.ico" type="image/vnd.microsoft.icon" /> </head> <body> <div id='content'><h3><center>Success! </center></h3> <table width='100%' border='0' cellspacing='1' cellpadding='0'><tr><td> <table width='100%' border='0' cellspacing='1' cellpadding='1'> <tr> <td align='center'><strong>User ID</strong></td> <td align='center'><strong>First Name</strong></td> <td align='center'><strong>Last Name</strong></td> <td align='center'><strong>Email</strong></td> </tr> <?php //create a connection $connection = mysql_connect("localhost","root","r2d2c3po"); //test the connection if (!$connection) { die("Database connection failed: " . mysql_error()); } //select database to use mysql_select_db("music_social_network", $connection); //setup query $sql = "SELECT * FROM `user` ORDER BY uid"; //get results from query or die $result = mysql_query($sql, $connection) or die(mysql_error()); //fetch rows from query $rows = mysql_fetch_assoc($result); // find out how many records there are to update $size = count($_POST['uid']); // start a loop in order to update each record $i = 0; while ($i < $size) { // define each variable $uid = $_POST['uid'][$i]; $fname = $_POST['fname'][$i]; $lname = $_POST['lname'][$i]; $email = $_POST['email'][$i]; // do the update and print out some info just to provide some visual feedback $query = "UPDATE `user` SET `fname` = '$fname', `lname` = '$lname', `email` = '$email' WHERE `uid` = '$uid' "; mysql_query($sql) or die ("Error in query: $query"); print " </tr> <td align='left'><p>$uid</p></td> <td align='left'>$fname</td> <td align='left'>$fname</td> <td align='left'>$lname</td> </tr> "; ++$i; } mysql_close($connection); ?> <tr> <td colspan='4' align='center'> </td> </tr> </table> </td> </tr> </table></div></body></html> Mod edit: [code] . . . [/code] tags added. Hey guys, Currently Im using the code: while($row=mysql_fetch_array($nt)){ echo" $row[user_login],$row[user_email],$row[CallHistoryTimes],$row[CallHistoryInfo],$row[CallHistoryLastOp]$row[CallHistoryLastDate],<br>"; } To list a mysql array; what I am trying to do is to use the mysql row ID and turn the entire string above into a list. So for example, the database would get the ID information & create a link of edit.php/?id=3 If someone could help me on this it would be mega appreciated. Thanks Hello everyone, Once again i need your help this time i want to know that, if I inserting multiple row by single insert query with the help of foreach (loop) then how to insert another cell value according to the name wise so that's way i send my hole code to you. <?php //include configuration file for connection include_once('config.php'); $sql = "select * from CLASS_STUDENT ORDER BY STUDENT_NAME ASC "; $result=mysql_query($sql); $count=mysql_num_rows($result); ?> <form name="form1" method="post" action=""> <table style="text-align: left; padding: 5px;" cellpadding="0px" cellspacing="0px"> <tbody> <tr> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Name</th> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Class</th> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Section</th> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Current Date</th> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Present</th> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Absent</th> <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Comment</th> </tr> <?php while($rows=mysql_fetch_array($result)) { ?> <tr> <td class="table1"> <? $id[] = $rows['STUDENT_NAME']; ?><? echo $rows['STUDENT_NAME'];?> </td> <td class="table1"> <input name="class[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['STUDENT_CLASS']; ?>"> </td> <td class="table1"> <input name="section[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['STUDENT_SECTION']; ?>"> </td> <td class="table1"> <input name="date[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['PRESENT_DATE']; ?>"> </td> <td id="present"> <input type="radio" name="present[<? echo $rows['STUDENT_NAME']; ?>]" checked="checked" value="PRESENT">Present </td> <td id="absent"> <input type="radio" name="present[<? echo $rows['STUDENT_NAME']; ?>]" value="ABSENT">Absent </td> <td style="text-align: left; padding: 5px; border: 1px #000000 solid; height: 33px;"> <input name="comment[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['COMMENT'];?>"> </td> </tr> <?php }?> <tr> <td colspan="7" style="vertical-align:middle; text-align: center;"><br><br> <input id="Submit" type="submit" name="Submit" value="Insert" style="text-align: center; background-color: #000000; color: #ffffff; border: 1px #000000 solid;"> </td> </tr> </tbody> </table> </form> <?php if(isset($_POST['Submit'])) { foreach($_POST['present'] as $id => $value) { $class=$_POST['class']; $section=$_POST['section']; $date=$_POST['date']; $comment=$_POST['comment']; $sql = "INSERT INTO ATTENDANCE(STUDENT_NAME, STUDENT_CLASS, STUDENT_SECTION, PRESENT_DATE, STUDENT_PRESENT, COMMENT) VALUES ('".$id."', '$class[$value]', '$section[$value]', '$date[$value]', '".$value."', '$comment[$value]') "; $result = mysql_query($sql); } } if($result) { header("location:Tea_home.php"); } else { //print_r ($_POST); echo "Your entry is not completed at this time............."; } ?> My result is comming just like that STUDENT_ID || STUDENT_NAME || STUDENT_CLASS || STUDENT_SECTION || PRESENT_DATE || STUDENT_PRESENT ||COMMENT 231 || PRASHANT KUMAR || || || 1/1/0001 12:00:00 AM || ABSENT || 230 || JYOTI NANDA || || || 1/1/0001 12:00:00 AM || PRESENT || 229 || TARUN NANDA || || || 1/1/0001 12:00:00 AM || PRESENT || 228 || RAVI KUMAR || || || 1/1/0001 12:00:00 AM || PRESENT || 227 || RAJIV KUMAR || || || 1/1/0001 12:00:00 AM || PRESENT || 226 || PRASHANT KUMAR || || || 1/1/0001 12:00:00 AM || ABSENT || 225 || JYOTI NANDA || || || 1/1/0001 12:00:00 AM || PRESENT || can you help me where i am writing wrong code i have created my own code of custom shopping cart
i have viewcart.php working great, now when i want to insert the orders from viewcart.php with list of like 5 items, how can i insert 5 names of products into my database 1 row
example
names are
1. jean
2. mond
3. richard
4. gwen
list above is the results of my while loop, now i want to insert those names to my database column[order_productname] so that i can identity what products are paid by my clients.
i tried fetch_array but if i assign variable to fetch array result, it only shows 1 which is "jean"
i wish this is possible
dforth
I've been using this code for a long time and realised it's very repetitive, but the id_column I want changes all the time function getRows() { $query = mysql_query($sql); $rows = array(); while($row = mysql_fetch_assoc($query)) { $rows[$row['id_column']] = $row; } return $rows; } So I wrote this function that will automatically create an array with a column I choose if I wish, but I'm not sure if it's very efficient. function getRows($query, $column_id = false) { $rows = array(); while($row = mysql_fetch_assoc($result)) { if($column_id === false) { $rows[] = $row; } else { if(isset($row[$column_id])) { $rows[$row[$column_id]] = $row; } else { $rows[] = $row; } } } return $rows; } I would appreciate some input as to make it better. Thanks. Hi All, I'm hoping someone can help me. I'm looking to create a form that will submit multiple entries to a MySQL database depending on the options selected on the form. I am able to submit single entries, but the multiple entire is a bit of a mystery to me. I have attached an JPG image to show an example of how I would like the form to work. I have also added the description/explination below to this example: Example 1 The form was completed and three access types were selected. Thus Three entries, all with the Same Reference number were captured into the database Example 2 The form was completed and Two Access Types were selected. Thus Two entries, all with the Same Reference number were captured into the database Example 3 The form was completed and Four Access Types were selected. Thus Four entries, all with the Same Reference number were captured into the database |
|||||||