|
PHP - Separating Mysql Concatenated Rows, While Maintaining Relationships To $row.
This is a LINK to the page -> http://shoutkey.com/carriage
I have a problem of separating MySQL concatenated info, yet still maintaining their relationship to other arrays in the same order. First off here is the MySQL code: Code: [Select] $query ="SELECT report,"; $query.="GROUP_CONCAT(DISTINCT docID) AS docIDs, "; $query.="GROUP_CONCAT(DISTINCT analyst) AS analysts, "; $query.="GROUP_CONCAT(DISTINCT region) AS regions, "; $query.="GROUP_CONCAT(DISTINCT country) AS countries, "; $query.="GROUP_CONCAT(DISTINCT topic) AS topics, "; $query.="GROUP_CONCAT(DISTINCT date) AS dates, "; $query.="GROUP_CONCAT(DISTINCT event) AS events, "; $query.="GROUP_CONCAT(DISTINCT link) AS links, "; $query.="GROUP_CONCAT(DISTINCT province) AS provinces "; $query.="FROM reports GROUP BY report ORDER BY dates DESC, docIDs DESC"; The most important thing is to get all attributes about each report. The thing is, each report can have as many records as it must, in order to store various types of information, like, if multiple analysts are working on the report, then each one saves their own record, but when retrieving these records MYSQL concatenates the records, to make it look to the analysts like this Report 1 --- Josh, Rebecca --- Philippines However, my problem is trying to display the reports by country... as you can see http://shoutkey.com/carriage. I have no idea about how to split up the countries, while maintaining their relationship to the $rows pulled from MySQL. I am gonna post the entire code, and let me know if anybody has any ideas. Code: [Select] $result = mysql_query($query); $numrows = mysql_affected_rows(); $current = false; while($row=mysql_fetch_assoc($result)) { $dates = $row['dates']; $analysts = $row['analysts']; $provinces = $row['provinces']; $topics = $row['topics']; $regions = $row['regions']; $countries = $row['countries']; $reports = $row['report']; $links = $row['links']; $events = $row['events']; if (!$current){ //first call echo "<h3><a href=\"#\">{$countries}</a></h3>"; //echo first call $current = $countries; echo "<div>" ;//open content div } elseif($current != $countries){ // country has changed echo "</div>"; //close previous content div echo "<h3><a href=\"#\">{$countries}</a></h3>"; //echo subsequent countries $current = $countries; echo "<div><p>" ;//open content div } echo $analysts." ----- ".$provinces." ----- ".$reports; echo "<br /><br />"; } echo "</div>"; ?> </div> Similar Tutorialshere i am again... so i have this algorithm that computes the current academic year for our school.. it outputs the correct academic year on a browser.. but when i try to insert it into my database, the data inserted goes wrong.. it just keeps on inserting -1 instead of the string 2011-2012 here's my code: Code: [Select] <?php $year = date('Y'); $currentyear = $year; $lastyear = $year - 1; $nextyear = $year + 1; if (date('m') < 6) { $current_ay = $lastyear."-".$currentyear; } else if (date('m') >= 6) { $current_ay = $currentyear."-".$nextyear; } echo $current_ay; $insertSQL = "INSERT INTO tbl_elections (election_id) values ($current_ay)"; mysql_select_db($database_organizazone_db, $organizazone_db); $Result1 = mysql_query($insertSQL, $organizazone_db) or die(mysql_error()); ?> *the database column to be inserted into is of varchar data type This topic has been moved to Other Libraries and Frameworks. http://www.phpfreaks.com/forums/index.php?topic=352357.0 I can create a one-to-one relationship between two tables by placing a unique constraint on the foreign key and making it NOT NULL, and make it one-to-at-most-one by removing the NOT NULL constraint. My question is what criteria should one use to determine which table holds the foreign key? For instance, I have the Car and Motor entities as shown below where one Car has one Motor, and conversely one Motor is used by one Car.
AbstractPart
Car extends AbstractPart What reasoning should one use to determine whether the Car table contain the Motor ID or should the Motor table contain the Car ID? Hello. I have a simple enough code that takes information from one table and drops it into another. This is great, but I have 2a new complexities that I have been unable to code correctly. A. 'lastname', 'firstname' on table1 need to be combined into 'name' How & Where do I combine these strings and then pass them? Code: [Select] <?php include('dbconfig.php'); // Make a MySQL Connection mysql_connect("localhost", "$user", "$password") or die(mysql_error()); mysql_select_db("$database") or die(mysql_error()); $result = mysql_query( "INSERT INTO table2 (lastname, firstname, email) SELECT lastname, firstname, email FROM table1 WHERE email='someemail@gmail.com' ") or die(mysql_error()); ?> im 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'm not sure if this is possible
Previously I had a problem of outputting rows of a specific query
This was the solution that was given to me
$txt .= $date . " " . $stratoparse . "\n\n";Where a while loop's results were concatenated to a single string What I would like to do is output the results in individual containers with controls such as dragging for repositioning / an x for deletion That's not what I'm here for in this post I want to embed youtube videos, so they are not just stored as links, rather a clickable video so if you have something like string.string2.string3.string4.string5.<iframe src="youtube"...>.string7.string8 how would that video be isolated and parsed / understood as a front end youtube video ? To provide a brief background in case you are wondering why in my right mind, I would want to do this... First it is the primary solution that comes to mind going off what already works Secondly, the purpose or point of the concatenated string comes from the "application" design which is a store and recall interface. Vague, I know. The website has multiple users and they all have access to a single database. They save random strings, whether they are paragraphs or links, possibly videos that are uploaded, etc... these are saved by a keyphrase, later recalled by searching that keyphrase. Currently the output is nothing more than a concatenated string, that was fine initially but some of my friends have been testing the website and they made suggestions which are good such as repositioning and deleting entries. Now I want to automatically parse youtube links into embedded videos... I'll probably use that explode "function" to disect the link and place the url within the src=" " Anyway, I have to look into this but if anyone has any thoughts I'd appreciate it. Edited by moose-en-a-gant, 11 January 2015 - 02:05 AM. How would I make a script that would delete any row in a MySQL table that was more than 7 days old? 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? 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 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 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. 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
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 "); 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. 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 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. 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 |
|||||||