|
PHP - Finding Next & Previous Id's In Mysql Results
I've got a page that displays all my blog entries on a single page. When you click on a specific entry, it pulls the post ID and loads the page specifically. From there, I wanted to have the option to click either onto the next or previous post by finding the next or previous ID in the database.. however, I know that sometimes posts are deleted, etc. so the ID's won't exactly be in order.
What direction should I take with this? Similar TutorialsHi, I have a select box that has every week day as an option. When a user picks "Monday" and submits the form I want to echo the date of monday last week. And if he picks tuesday, I want it to give the date of Tuesday last week, on so on. How would I do that? Thank you in advance! I'm trying to figure out how to filter the results of a database query. For example somebody uses a php/javascript form to search for ford cars. When they see 100 results, they then narrow the results by model and/or color. Am I right in thinking that the way to do this is by creating a temporary table of the results? What is the best approach to what I'm trying to do? Hi all, I am new to PHP and am trying to implement a window that opens when a user is trying to validate an organisation on our site. I have an initial page where users can enter data. One of the fields is organisations. This has a button to its right that lets the user search existing organisations in the mysql db. I have the search piece thanks to some code I found and altered. However, instead of a static non-linked list 1,2,3 coming back from the search, I need the search results to be links / or at least "select"-able. When the user chooses the organisation they were referring to that exists in the db I would like the selection to go back to the original php page and populate the organisation field. Ideally without having other data in the original form being lost. Any tips, hints or links would be greatly appreciated. Here is my code: <?php $dbHost = 'localhost'; $dbUser = 'root'; $dbPass = 'mypass'; $dbDatabase = 'mydb'; $con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error()); mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error()); // Set up our error check and result check array $error = array(); $results = array(); // First check if a form was submitted. // Since this is a search we will use $_GET if (isset($_GET['search'])) { $searchTerms = trim($_GET['search']); $searchTerms = strip_tags($searchTerms); // remove any html/javascript. if (strlen($searchTerms) < 3) { $error[] = "Search terms must be longer than 3 characters."; }else { $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection. } // If there are no errors, lets get the search going. if (count($error) < 1) { $searchSQL = "SELECT Organisation_Name FROM vcaccount_contact_organisation WHERE "; // grab the search types. $types = array(); $types[] = isset($_GET['Organisation_Name'])?"`Organisation_Name` LIKE '%{$searchTermDB}%'":''; $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked) if (count($types) < 1) $types[] = "`Organisation_Name` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked $andOr = isset($_GET['matchall'])?'AND':'OR'; $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `Organisation_Name`"; // order by organisation. $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}"); if (mysql_num_rows($searchResult) < 1) { $error[] = "The search term provided {$searchTerms} yielded no results."; }else { $results = array(); // the result array $i = 1; while ($row = mysql_fetch_assoc($searchResult)) { $results[] = "{$i}: {$row['Organisation_Name']}<br /><br />"; $i++; } } } } function removeEmpty($var) { return (!empty($var)); } ?> <html> <title>My Simple Search Form</title> <style type="text/css"> #error { color: red; } </style> <body> <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> Organisation: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br /> <input type="submit" name="submit" value="Search!" /> </form> <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?> </body> </html> Okay, I'm wanting to do a search, but until return the info if it has a specific indicator in the chosen field. Let me show you: $query = doquery("SELECT * FROM `orgs` IF `type`='c' ORDER BY name", "orgs"); How do I do that "IF" bit? Obviously this doesn't work. Thanks! Hi all, I have a fairly simple problem, but i cannot get to it. I have a page, that displays one (1) record at the time, and I want to have forward and back buttons (links) at the top and bottom of the page. Can anyone help me with that??? Here is a part of my code. Code: [Select] <form method="get"> <table border="0" width="90%" cellpadding="2" cellspacing="2" align="center"> <tr><td align="center"> Select first letter or kind of the story: <input type="hidden" name="link" value="stories.php"> <input type="hidden" name="Pripadnost" value="3"> <select name="letter" onchange="this.form.submit()"> <?PHP $letter = trim($_REQUEST["letter"]); ?> <option value="Sve" <?PHP echo $letter == "All" ? " selected " : "" ; ?>>All</option> <?PHP include_once ("/includes/databaseMySQL.php"); mysql_set_charset('utf8'); $SQL = "Select distinct ucase(left(title,1)) as sl FROM tblStoryes order by sl"; $rs_data = mysql_query($SQL) or die(mysql_error()); while ($row_data = mysql_fetch_assoc($rs_data)) { ?> <option value="<?PHP echo $row_data["sl"]; ?>"<?PHP echo $letter == $row_data["sl"] ? " selected " : "" ; echo ">".$row_data["sl"] ;?></option> <?PHP // ****************** Listed all the first letters of the titles into a drop box. *********************** // } ?> </select> Bosnian <input type="checkbox" name="Bosnian " <?PHP echo isset($_REQUEST["Bosnian "]) ? "checked=checked" : "" ;?> onclick="this.form.submit()" /> Turkish <input type="checkbox" name="Turkish " <?PHP echo isset($_REQUEST["Turkish "]) ? "checked=checked" : "" ;?> onclick="this.form.submit()" /> Arabian <input type="checkbox" name="Arabian " <?PHP echo isset($_REQUEST["Arabian "]) ? "checked=checked" : "" ;?> onclick="this.form.submit()" /> </td></tr> </table> <table border="0" width="90%" cellpadding="2" cellspacing="2" align="center"> <?PHP $id = -1; $rs_data = mysql_query("Select max(id) as maxid from tblStories WHERE aktivno = True") or die(mysql_error()); $row_data = mysql_fetch_assoc($rs_data); $maxid = (int) $row_data["maxid"]; // ****************** Found the largest ID from the table. *********************** // $SQL = "SELECT * FROM tblStories WHERE aktivno = True "; if ( strlen($letter) < 3 && strlen($letter) > 0 ) { $SQL = $SQL . " and ucase(left(`title`, 1)) = '" . $Letter . "'"; } $lang = 0; $lang = $lang + isset($_REQUEST["Bosnian"]) * 4; $lang = $lang + isset($_REQUEST["Turkish"] ) * 2; $lang = $lang + isset($_REQUEST["Arabian"] ); switch ($lang) { case 6: // bos i tur $SQL = $SQL . " and ( Bosanski = 1 or Turski = 1 ) "; break; case 5: // bos i ar $SQL = $SQL . " and ( Bosanski = 1 or Arapski = 1) "; break; case 4: // bos $SQL = $SQL . " and Bosanski = 1 "; break; case 3: // tur i ar $SQL = $SQL . " and ( Turski = 1 or Arapski =1) "; break; case 2: // tur $SQL = $SQL . " and Turski = 1 "; break; case 1: // ar $SQL = $SQL . " and Arapski =1 "; break; } if (trim($_REQUEST["DBid"]) <> "" or $_REQUEST["DBid"] > 0) $SQL = $SQL . " and id > " . trim($_REQUEST["DBid"]) ; $SQL = $SQL . " LIMIT 1"; //order by naslov $rs_data = mysql_query($SQL) or die(mysql_error()); echo "<!--" . $SQL . " : Jezik je:" . $lang . "-->"; while ($row_data = mysql_fetch_assoc($rs_data)) { $id = (int) $row_data["ID"]; ?> <tr> <td align="center" colspan=3> <h3><?PHP echo $row_data["Title"];?> </h3> </td> </tr> <tr> <td width=20%> </td> <td align="justify" width=60%> <?PHP echo $row_data["tekst"]; // tekst = text, but not keyword ?> </td> <td width=20%> </td> </tr> <?PHP } ?> </table> <p> <?PHP // ****************************** HEEEEELLLLPPPPPP ******************************* // ?> <table border="0" width="90%" cellpadding="1" align="center"> <tr> <td align="center"><a href="?DBid=1<?PHP foreach($_REQUEST as $arr => $elem){ if ( $arr != "DBid") echo "&" . $arr . "=" . $elem; } ?>"> <<</a></td> <td align="center"><a href="?DBid=<?PHP echo $_REQUEST["id"]; foreach($_REQUEST as $arr => $elem){ if ( $arr != "DBid") echo "&" . $arr . "=" . $elem; } ?>" ><</a></td> <?PHP if ( $maxid <= $id ) { ?> <td align="center">></td> <td align="center">>></td> <?PHP } else {?> <td align="center"><a href="?DBid=<?PHP echo $id ; foreach($_REQUEST as $arr => $elem){ if ($arr != "DBid") echo "&" . $arr . "=" . $elem; } ?>">></a></td> <?PHP if ( ($id + 9) < $maxid ) { ?> <td align="center"><a href="?DBid=<?PHP echo $id+10 ; foreach($_REQUEST as $arr => $elem){ if ($arr != "DBid") echo "&" . $arr . "=" . $elem; } ?>">>></a></td> <?PHP } else {?> <td align="center">>></td> <?PHP }?> <?PHP }?> </tr> </table> </form> I want to loop through a MySQL table, and perform a calculation based on the current row the loop is on and the previous loop. Say my table has 2 columns - Film_id and FilmRelease, how would I loop through and echo out a calculation of the current FilmRelease and the previous row's column value? Thanks guys I have got to this stage, but for some reason it's not printing out anything Code: [Select] <?php mysql_connect("localhost", "****", "*****") or die(mysql_error()); mysql_select_db("*****") or die(mysql_error()); $sql = mysql_query("SELECT FilmRelease FROM Films_Info") or die(mysql_error()); $last_value = null; while ($row = mysql_fetch_assoc($sql)) { if (!is_null($last_value)) { print date_diff($row['FilmRelease'], $last_value) . "<BR>"; } $last_value = $row['FilmRelease']; } Hi: I'm a newby regarding uploading files to MySQL, turning report output into an HREF, and getting MySQL data via a hyperlink. I have successfully uploaded, files to MySQL, I have also been able to display filename information as a hyperlink in report output, but when I click on the hyperlink, I get the following message format on a 404 page: The requested URL /current_dir_of_requesting_page/filename.filetype was not found on this server. My reporting page has teh following line of code in a report table to create the hyperlink: <td style="width:225px"><? echo "<a href=".$row['name'].">".$row['name']."</a>";?></td> Can anyone assist me with this? I am trying to make a filter to show posts from mutual friends between you and the persons profile you're on, I have somewhat of an idea on how to write the mysql query (it needs to be just one query, not two(which would be the easy way of doing it)). the "friends" table has: "friend_1" and "friend_2" and the "posts" table: "to_id" and "from_id" The PHP variables would be: "$session" and "$id" id would be the users Id of the profile you're on. Thanks, any help would be appreciated. I have two button that load the next and previous pages of friends, I'm having trouble doing so, it sort of works, but it's got bugs so it's not right. Next: Code: [Select] $query = mysql_query("SELECT * FROM friends WHERE (friend_1='".$id."' OR friend_2='".$id."') AND id>$last_id ORDER BY id ASC LIMIT 16");previous: Code: [Select] $query = mysql_query("SELECT * FROM friends WHERE (friend_1='".$id."' OR friend_2='".$id."') AND id<$last_id ORDER BY id ASC LIMIT 16"); $id = id of the users profile. $last_id = the last loaded friend id (unique id to friends table , not the friends actual id) Hello, This is query in MySQL Quote mysql> LOAD DATA LOCAL INFILE '/var/www/html/numbers.csv' INTO TABLE details FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (number); Query OK, 0 rows affected (0.00 sec) Records: 2200 Deleted: 0 Skipped: 1200 Warnings: 0 Now, I am trying to run the same query in PHP and display the same results.... (i.e. Records, Deleted, Skipped) Quote <?php $db=mysql_connect("localhost", "user", "1234") or die(mysql_error()); $dname="database"; mysql_select_db($dname)or die(mysql_error()); $sqlstatement="LOAD DATA LOCAL INFILE '/var/www/html/numbers.csv' INTO TABLE details FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (number)" ; mysql_query($sqlstatement) or die(mysql_error());; echo "it is done!"; ?> Tried mysql_fetch_array, row but could not get it to work... please help guys Hey everyone. So I am coding a badge system for my website usersystem. I am trying to think how I'll handle issues before actually starting to code it and one issue I just can't seem to figure out how to handle.. How would I limit the amount of MySQL results to show per line. Eg. A user has 6 badges but I'd only like to show 2 per line. So in total it'd show 3 lines with 2 badges per line. If that makes any sense? Is there any way possible to actually do this? What's the best way to format results from a MySQL table? I've found very vague examples of PHP code utilizing html tables. Below is my current PHP. Thanks! <?php $con = mysql_connect("localhost","xxxxx","xxxxx") or die('Could not connect: ' . mysql_error()); mysql_select_db("addresses", $con); $result = mysql_query("SELECT * FROM addresses"); while($row = mysql_fetch_array($result)) { echo $row['first_name'] . " " . $row['last_name'] . " " . $row['extra_info'] . " " . $row['address'] . " " . $row['city'] . " " . $row['state'] . " " . $row['zip']; echo "<br />"; } mysql_close($con); ?> I'm working on a website that uses a database to display the employees from different states. Below is the code I'm using and everything works great, but I can't figure out why all the results are getting posted twice. I'm trying to isolate just one state so we can have a different contact page for each state. Thanks for any help! Please let me know if you need more information. (I'm attaching a screenshot of what's happening as well.) Code: [Select] <? include "public/public_common.php"; //Open Database Connection $db = open_db_connection(); $sql = "SELECT DISTINCT state_full = 'california' from users where active = 'Y' and show_on_contactus = 'Y'"; $results = mysql_query($sql, $db); if(!mysql_num_rows($results) == FALSE) { while($row = mysql_fetch_array($results, MYSQL_BOTH)) { echo "<ul id=\"directors\">\n"; $state_sql = "SELECT * from users where active = 'Y' and show_on_contactus = 'Y' and state_full = 'california' order by last_name"; $state_results = mysql_query($state_sql, $db); if(!mysql_num_rows($results) == FALSE) { while($state_row = mysql_fetch_array($state_results, MYSQL_BOTH)) { echo "<li>\n"; echo " <div class=\"fl dirimg\">\n"; if($state_row[photo] == "") { echo "<img border=\"0\" width=\"61\" height=\"85\" src=\"images/exe_placeholder.jpg\" alt=\"$state_row[first_name] $state_row[last_name]\" />\n"; } else { echo "<img border=\"0\" width=\"61\" height=\"85\" src=\"n2team/pictures/$state_row[photo]\" alt=\"$state_row[firstname] $state_row[last_name]\" />\n"; } echo " </div>\n"; echo " <span class=\"fr\">$state_row[first_name] $state_row[last_name]<br />\n"; echo " $state_row[title]<br />\n"; echo " $state_row[city], $state_row[state]<br />\n"; //echo " <a href=\"mailto:$state_row[email]\">$state_row[email]</a></span></li>\n"; } } echo "</ul>\n"; } } ?> I have a problem displaying the results of a table, It shows them in one long list and I'd like them to show as 2 or 3. I had a look around and the most favoured way to this I found was to split the results either by odd/even or using percentages but Im having trouble implementing it into my script. How would I go about adding the odd/even way to this: <?php $query = mysql_query('SELECT * FROM users ORDER BY Username'); while ($row = mysql_fetch_array($query)) { if ($row['UserID']) ?> <b>Username:</b> <span class=class1><a href="aeditprofile.php?username=<? echo $row['Username'] ?>"><? echo $row['Username'] ?></a></span><br> <b>Group:</b> <? echo $row['Level'] ?></color><br /><hr> <?php } ?> Essentially I'm after getting something like this: Name Name Name Group Group Group and so on down the page.... How can I limit the amount of time my while loop to only show the first 4 rows of my SQL table?
I am using this code to loop my whole table:
while($row = $results->fetch(PDO::FETCH_ASSOC))
{
echo '
<li>Mileage: '.number_format($row["Mileage"]).'</li>
<li>Engine size: '.$row["EngineSize"].'cc</li>
';} ?>
I only want to loop through the first 4 rows of my SQL table, I then want to duplicate the same code but start at the 5th row until the 8th row of the table, how can I do this?
Thanks, Nickthis is my code but i want to have a pice so if their is no results the it will put in a message like "No Results" but i cant figger it out <?php $host="localhost"; $username="xxxxx"; $password="xxxxxx"; $db_name="xxxxxxx"; $tbl_name="Shows"; // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT * FROM $tbl_name WHERE Month='April'and Year='2011' order by Day asc "; $result=mysql_query($sql); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td> <table width="400" border="1" cellspacing="0" cellpadding="3"> <?php while($rows=mysql_fetch_array($result)){ ?> <span class="elevenbold"><? echo $rows['Day']; ?> <? echo $rows['Month']; ?> <br><strong class="elevenboldblue"><? echo $rows['Song']; ?></strong><br><? echo $rows['Data']; ?></span><br><br> <?php } ?> </table> </td> </tr> </table> <?php mysql_close(); ?> Hi guys, i'm trying to search from a form and use php to display the results of the FullText Search from a MySQL database, but I don't get any results from the query. This is the query: $query="SELECT * FROM tutorials WHERE MATCH(title, tags) AGAINST ('$searchform')"; I think the query is right but it doesn't give me any results.... Does anyone know what could be wrong? I think it might be something wrong with my table (database). my table should have 5 columns: id, title, category, content, tags The form should search throught the title and tags columns to see if it finds anything matching the $searchform , but I don't think it is doing it. Can anyone help please? This topic has been moved to Application Design. http://www.phpfreaks.com/forums/index.php?topic=347295.0 |
|||||||