|
PHP - Using Dropdown To Limit Rows
I am trying to include a dropdown menu on a page so that a user can choose how many records will be shown and return that data back to the same page. what I have so far is....
The Form Code: [Select] <form action="PHP_SELF" method="post" name="records_per_page" target="_self"> Number of Records per page <select name="records_per_page" id="records_per_page"> <option value="5">5</option> <option value="10">10</option> <option value="15">15</option> <option value="20">20</option> <option value="25">25</option> <option value="30">30</option> <option value="40">40</option> <option value="50">50</option> </select> </form> The Query // How many rows to show per page $rowsPerPage = 25; // Retrieve all the data from the seasons table $result = mysql_query ("SELECT *, DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons LIMIT "; if ($_POST['records_per_page'] = '') $result .= "'$rowsPerPage' "); else $result .= "'$records_per_page' "); or die(mysql_error()); The error I get is Code: [Select] Parse error: syntax error, unexpected ';' in /homepages/46/d98455693/htdocs/content/test/seasonslist2.php on line 28which refers to the line LIMIT "; Having posted the code into Dreamweaver it also shows errors for the two options in the if statement $result .= "'$rowsPerPage' "); $result .= "'$records_per_page' "); so it is clearly that I have set the code up incorrectly, probably with misplacement of ;'s. What I am trying to do is when the page loads normally, the records should be limited to $rowsPerPage but if a user has specified a number of rows in the dropdown it should show that number. Once I have that sorted I will also be trying to implement a'Start from Record Number' option. As always, any advice would be immensely appreciated! Thanks in advance Steve Similar TutorialsHi guys I have a problem. I limit the displayed rows by 10 (which works) and then a link saying "next" or "previous" The link itself works, but the site always just displays teh first 10 data entries. What do I do wrong? Thank you! Code: [Select] <?php $host="****"; // Host name $username="****"; // Mysql username $password="****"; // Mysql password $db_name="****"; // Database name $tbl_name="sp_users"; // Table name // 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 ORDER BY user_id"; $sql="SELECT * from sp_users,sp_schools where sp_users.user_id=sp_schools.school_id ORDER BY school_name LIMIT 0, 10"; $result=mysql_query($sql); $num_rows=mysql_num_rows($result); ?> <?PHP //check if the starting row variable was passed in the URL or not if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) { //we give the value of the starting row to 0 because nothing was found in URL $startrow = 0; //otherwise we take the value from the URL } else { $startrow = (int)$_GET['startrow']; } ?> <?PHP //this part goes after the checking of the $_GET var $fetch = mysql_query("SELECT * FROM sp_users,sp_schools where sp_users.user_id=sp_schools.school_id LIMIT $startrow, 10")or die(mysql_error()); ?> <style type="text/css"> <!-- .style2 {font-weight: bold} .style3 { font-family: Arial, Helvetica, sans-serif; color: #000000; } .style10 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #000000; } #Layer1 { position:absolute; left:803px; top:36px; width:65px; height:28px; z-index:1; } #Layer2 { position:absolute; left:707px; top:19px; width:143px; height:39px; z-index:1; } #Layer3 { position:absolute; left:247px; top:463px; width:175px; height:53px; z-index:2; } --> </style> <title>User overview</title> </html> <div id="Layer2"> <form action="<?= $_SERVER['PHP_SELF'] ?>" method="post"> <input type="hidden" name="foo" value="<?= $foo ?>" /> <input type="submit" name="submit" value="Refresh" /> </form></div> <div id="Layer3"><?PHP //now this is the link.. echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+10).'">Next</a>'; ?> <?PHP $prev = $startrow - 10; //only print a "Previous" link if a "Next" was clicked if ($prev >= 0) echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'">Previous</a>'; ?></div> <table width="779" border="0" align="left" cellpadding="0" cellspacing="1" bgcolor="#996600"> <tr> <td width="777"> <div align="left"> <table width="779" border="1" cellspacing="0" cellpadding="3"> <tr> <td colspan="6" align="center"><div align="center" class="style1 style3"><strong>SchoolPorta.com Users / Total: <?php echo $num_rows ?></strong></div></td> </tr> <tr> <td width="342" align="center"><span class="style2">school</span></td> <td width="62" align="center"><span class="style2">Name</span></td> <td width="104" align="center"><span class="style2">Lastname</span></td> <td width="130" align="center"><span class="style2">Email</span></td> <td width="64" align="center"><span class="style2">Update</span></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td><span class="style10"><? echo $rows['school_name']; ?></span></td> <td><span class="style10"><? echo $rows['user_first_name']; ?></span></td> <td><span class="style10"><? echo $rows['user_surname']; ?></span></td> <td><span class="style10"><a href="mailto:<?php echo $rows['user_login']; ?>"><?php echo $rows['user_login']; ?></a></span></td> <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style10">update</a></td> </tr> <?php } ?> </table> </div></td> </tr> </table> <div align="left"> <p> </p> <p> </p> <p> </p> <p> <?php mysql_close(); ?> </p> </div> Hi all, I need your help as I have got a problem with display the 50 rows in the search results. I want to limit 50 rows in per page and if I have less than 50 rows like 21 rows in the page 2 then display the 21 rows. I have got a problem with the limit, because when I tried to use `LIMIT 0, 50`, it will display total 71 rows which I only want to display no more than 50 in per page. Here is what I have tried:
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT 0, 50");
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT $offset, $limit");
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
Here is the full code:
<?php
// Initialize the session
session_start();
//Connect to the database
require_once('config.php');
$searchemail = '%' . 'gmail.com' . '%';
$searchsubject = '%' . 'gmail.com' . '%';
$searchmessage = '%' . 'gmail.com' . '%';
$mailbox = $link->prepare("SHOW TABLES");
$mailbox->execute();
$folders = array();
$total = 0;
$total_rows = 0;
$i = 0;
while ($folder = $mailbox->fetch(PDO::FETCH_NUM)) {
$folder = $folder[0];
if (strpos($folder, 'users') !== false) {
$folder = str_replace('users', '', $folder);
}
else
{
$folders[$i] = $folder;
}
$i++;
}
foreach($folders as $folder) {
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT 0, 50");
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
if ($search_mailbox->rowCount() > 0) {
$total += $search_mailbox->rowCount();
}
}
$page = 1;
$limit = 50;
//$per_page = 50;
//$pages = ceil($total / $per_page);
$offset = ($page - 1) * $limit;
foreach($folders as $folder) {
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT $offset, $limit);
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
if ($search_mailbox->rowCount() > 0) {
foreach($search_mailbox->fetchAll() as $k => $row) {
$email_number = $row['id'];
$search_from = $row['from_email'];
$search_subject = $row['subject'];
$total_rows++;
echo $search_subject . '.........................' . $total_rows;
echo "<br>";
}
}
}
?>
I am unable to fetch the first 50 rows in the page 1 and I am also unable to fetch the 21 rows in the page 2. What I am trying to achieve is when I am in page 1, I want to display the first 50 rows, then in the page 2 if I have another 50 rows then display the 50 rows, but if I have less than 50 rows like 21 rows then display the 21 rows. Can you please show me an example how I can start with first 50 rows when I am in the page 1 then in page 2 display 21 rows to make in total 71 rows? Any advice would be much appreicated. Thanks in advance. I am building a commenting system, it's sort of like facebooks comments. I have it right now so it loads the first last 3 comments in the database, and then there's a button to view all comments. I have it working fine, except when you add another comment to it, and then click the view all comments, This is the script that loads onload showing the last 3 comments $limit = 3; $query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$id' ORDER BY id DESC LIMIT $limit"); This is the php script that runs when you click view more //($start_id would be 3 in this case) $query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$comment_id' ORDER BY id DESC LIMIT $start_id, 1000"); so to try to show you what's happening: OnLoad--------------------------------|---When you add more comments, (let's just say u add 1 comment before u click view comments)---- comment 9 --------------------------| comment 10 comment 8 --------------------------| comment 9 comment 7 --------------------------| comment 8 ------------------------------------------| comment 7 ----to be loaded onclick-------------|------What's loaded onclick--- comment 6 --------------------------| comment 7 comment 5 --------------------------| comment 6 comment 4 --------------------------| comment 5 comment 3 --------------------------| comment 4 comment 2 --------------------------| comment 3 comment 1 --------------------------| comment 2 ------------------------------------------| comment 1 (see how "comment 7" is being shown twice in a row) I have a commenting system and i have a limit of a certain number of comments to be shown. What i want to do is have a button on the bottom of the page at the end of the comments that are showing and when you click it ajax loads the next certain number of of rows (but not all of them),and then you click it again and it shows more of them, etc. So for example. comment 1 comment 2 comment 3 comment 4 --click button--(loads 4 more)--- comment 5 comment 6 comment 7 comment 8 --click button--(loads 4 more)-- comment 9 comment 10 comment 11 comment 12 etc. until there are no more rows. what's the best way to do this? (I know how to do the ajax and all, i just need help with the script to select the rows) Thanks. Unless buffer overflows or breaking out of code to perform a new command are problems that have been solved.... I am trying to figure out the proper PHP method for setting a boundary on a variable within a script. I have this variable $name which is fed a value from $_POST['name'] from a form field. Now this form field is limited in the HTML to accept only 20 characters, but someone could easily edit the form or outgoing post data. So I want to know how to limit the variable size in the script. In other languages it could be something like this: var name(20). So how do I do that in PHP? what Im basically trying to do is just like a phpmyadmin function... you select rows you want to update with a checkbox and then it takes you to a page where the rows that are clicked are shown in forms so that you can view and edit info in them... and then have 1 submit button to update them all at once. I have 2 queries that I want to join together to make one row
I have a form with dropdown list that is populated with values from Sql Server table. Now i would like to use this selected item in SQL query. The results of this query should be shown in label or text field. So when a user selects item from dropdown menu, results from SQL query are shown at the same time. I have two dropdown list at the moment in my form. First one gets all values from a column in table in SQL Server. And the second one should get a value from the same table based on a selection in first dropdown list.
When i load ajax.php i get 2 error mesages: This is my code so far. I have tried to do it with this Ajax script. But i can only get first dropdown to work. The second dropdown(sub_machinery) does not show values, when first dropdown item is selected. The second dropdown should show values from databse table with this query( *$machineryID* is first dropdown selected item): SELECT MachineID FROM T013 WHERE Machinery=".$machineryID. Index.php
<!doctype html>
<?PHP
$server = "server";
$options = array( "UID" => "user", "PWD" => "pass", "Database" =>
"database");
$conn2 = sqlsrv_connect($server, $options);
if ($conn2 === false) die("<pre>".print_r(sqlsrv_errors(), true));
echo " ";
?>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>
<body>
<section id="formaT2" class="formaT2 formContent">
<div class="row">
<div class="col-md-2 col-3 row-color remove-mob"></div>
<div class="col-md-5 col-9 bg-img" style="padding-left: 0;
padding-right: 0;">
<h1>Form</h1>
<div class="rest-text">
<div class="contactFrm">
<p class="statusMsg <?php echo
!empty($msgClass)?$msgClass:''; ?>"><?php echo $statusMsg; ?></p>
<form action="connection.php" method="post">
<div>machinery</div>
<select id="machinery">
<option value="0">--Please Select Machinery--</option>
<?php
// Fetch Department
$sql = "SELECT Machinery FROM T013";
$machanery_data = sqlsrv_query($conn2,$sql);
while($row = sqlsrv_fetch_array($machanery_data) ){
$id = $row['Id'];
$machinery = $row['Machinery'];
// Option
echo "<option value='".$id."' >".$machinery."</option>";
}
?>
</select>
<div class="clear"></div>
<div>Sub Machinery</div>
<select id="sub_machinery">
<option value="0">- Select -</option>
</select>
<input type="submit" name="submit"
id="submit" class="strelka-send" value="Insert">
<div class="clear"> </div>
</form>
</div>
</div>
</div>
</div>
</section>
</script>
<script type="text/javascript">
$(document).ready(function(){
$("#machinery").change(function(){
var machinery_id = $(this).val();
$.ajax({
url:'ajaxfile.php',
type: 'post',
data: {machinery:machinery_id},
dataType: 'json',
success:function(response){
var len = response.length;
$("#sub_machinery").empty();
for( var i = 0; i<len; i++){
var machinery_id = response[i]['machinery_id'];
var machinery = response[i]['machinery'];
$("#sub_machinery").append("<option
value='"+machinery_id+"'>"+machinery+"</option>");
}
}
});
});
});
</script>
</body>
</html>
Ajaxfile.php
<?php
$server = "server";
$options = array( "UID" => "user", "PWD" => "pass",
"Database" => "database");
$conn2 = sqlsrv_connect($server, $options);
if ($conn2 === false) die("<pre>".print_r(sqlsrv_errors(), true));
echo " ";
$machineryID = $_POST['machinery']; // department id
$sql = "SELECT MachineID FROM T013 WHERE Machinery=".$machineryID;
$result = sqlsrv_query($conn2,$sql);
$machinery_arr = array();
while( $row = sqlsrv_fetch_array($result) ){
$machinery_id = $row['ID'];
$machinery = $row['MachineID'];
$machinery_arr[] = array("ID" => $machinery_id, "MachineID" =>
$machinery);
}
// encoding array to json format
echo json_encode($machinery_arr);
?>
Edited May 6, 2019 by davidd
Hi freaks, I'm new to php first of all. I'm dynamically binding a dropdownlist with mysql database . After the user selects an item from it , I want to match that item with another table so as to populate another database. The code I'm using to populate dropdown: Code: [Select] <?php $con = mysql_connect("localhost","root",""); if(!$con) { die ('Can not connect to : '.mysql_error()); } mysql_select_db("ims",$con); $result=mysql_query("select cat_id,cat_name from category"); echo "<select name=cat>"; while($nt=mysql_fetch_array($result)) { echo "<option value=$nt[cat_id]> $nt[cat_name] </option>"; } echo "</select>"; mysql_close($con); ?> Now after the user selects any one of the item , I want to bind another dropdown on the same page using such query like $result=mysql_query("select subcategory.sc_id,subactegory.sc_name from subcategory,category where subcategory.sc_id=$nt[cat_id]"); Please anyone tell me the logic and code to do it. Also tell me do I need an intermediate page to post the 1st dropdown value and then continue with 2nd dropdown. I couldn't figure out the concept anyhow. Help on this will be highly appreiable . (Tell me if I'm not clear with my question) im using the floowing code to pull all alerts from the database. as you can see i have 3 types of alertsd. Profile alerts, forum alerts and topic alerts. They are sorted and placed on the screen under their respective header. However i want to limit it so it only displays 4 of each type of alert. I cant use LIMIT in the query because that would limit all alerts meaning only 4 alerts in total would show up and i just need to limit each alert type. any ideas? $alert_query = $link->query("SELECT a.a_aid, a.a_alert_type, a.a_time_alerted, a.a_fid, a.a_poster, a_alert_read, a.a_tid, c.f_name as cat_name, f.f_fid, f.f_name, t.t_name, u.u_avatar, u.u_avatar_cropped FROM ".TBL_PREFIX."alerts as a LEFT JOIN ".TBL_PREFIX."forums as f ON (f.f_fid = a.a_fid) LEFT JOIN ".TBL_PREFIX."topics as t ON (t.t_tid = a.a_tid) LEFT JOIN ".TBL_PREFIX."forums as c ON (c.f_fid = f.p_id) LEFT JOIN ".TBL_PREFIX."users as u ON (u.u_username = a.a_poster) WHERE a.a_user_name = '$user_name' ORDER BY a_time_alerted ") or die(print_link_error()); $alert_info = $alert_query->fetchAll(); $pm_alert_list = ''; $num_pm_alerts = 0; $num_forum_alerts = 0; $num_topic_alerts = 0; foreach($alert_info as $key => $val) { $alert_info[$key]['a_alert_read'] == 0 ? $color = '#f5dfaf' : $color = '#f4f4f4'; // if alert is a profile message alert if($alert_info[$key]['a_alert_type'] == 1) { $pm_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">'; $pm_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" />'.profile_link($alert_info[$key]['a_poster']).' posted on your wall</p>'; $pm_alert_list .= '<p class="alert_time"> on '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>'; $pm_alert_list .= '</dd>'; $num_pm_alerts++; } if($alert_info[$key]['a_alert_type'] == 2) { $forum_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">'; $forum_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" /><strong><a href="'.$config['asf_root'].'category/'.create_url($alert_info[$key]['cat_name']).'/forum/'.create_url($alert_info[$key]['f_name']).'">'.$alert_info[$key]['f_name'].'</a></strong> has a new topic</p>'; $forum_alert_list .= '<p class="alert_time"> '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>'; $forum_alert_list .= '</dd>'; $num_forum_alerts++; } if($alert_info[$key]['a_alert_type'] == 3) { $topic_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">'; $topic_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" /><strong><a href="'.$config['asf_root'].'category/'.create_url($alert_info[$key]['cat_name']).'/forum/'.create_url($alert_info[$key]['f_name']).'/topic/'.create_url($alert_info[$key]['t_name']).'">'.$alert_info[$key]['t_name'].'</a></strong> has a new post</p>'; $topic_alert_list .= '<p class="alert_time"> '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>'; $topic_alert_list .= '</dd>'; $num_topic_alerts++; } } This code works fine without the LIMIT 5, but it lists all the results. With the LIMIT Code: [Select] $query = 'SELECT * FROM wp_playerRank WHERE year="2011" LIMIT 5 ORDER BY rankClass ASC'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) { Here is the error I'm getting: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/jwrbloom/public_html/resources/players/rank2011_top5.php on line 26 Hello friends, if i've database table (mytable) has the following ids 1 2 3 4 5 6 and i want to get it with limit the first (1 to 3 ) only Code: [Select] $sql ="select * from mytable LIMIT 3"; this will show the first 3 (1,2,3) how then i write code that shows which is after 3 so it shows me 4 5 6 and if there any way i can say Code: [Select] $sql ="select * from mytable LIMIT (first half of ids)"; and (shows 1,2,3..ect till half) Code: [Select] $sql ="select * from mytable LIMIT (second half of ids)"; will (shows 4,5,6...ect till end) thank you I am in need of a alimit on the explode() fucntion but reading through the php explode manual on php.net there doesn't seem to be one that is right for me. Basically I'm making a command system for a php chat and it has a command called ban. I have code that find out if it is a command or a normal string, I also have code that starts to break up the command into segments that can be places in the database. I want to split up "ban user lengthID "Reason why you were banned"" using explode($string, " ") but I notice that that will also break up the "reason why you are banned". How do I get it to split up the command but NOT thereason why you were banned string? Confusing I know, Please ask any questions that would help me explain it better Thanks in advance! Hi, This line of code works just fine for me: $sql_quest = 'SELECT id, username FROM user ORDER BY id ASC LIMIT 0, 30'; I want to be able to set the integer values dynamically and I thought something like this would work: $sql_quest = 'SELECT id, username FROM user ORDER BY id ASC LIMIT' . $my_int_value . ',' . $my_int_value + 30; But it doesn't... Any ideas? hello, I have a form i am using for a raffle that i have weekly. the problem is i want to limit how many times they can register. I am using a basic form that collects name, email, and IP ( which is hidden ). When it gets submitted there is time stamp as well using this date("m/d/y : H:i:s", time() I would like to restict registering by one hour at a time. can you please give me some help with this. I have thought of maybe using cookies, but some of my smarter members can bypass that. So i think i will either use email or IP . any help would be very appreciated hello, is there a wat to limit the following to only the first 25 characters? $customername=$row1["customer"]; thanks Is there a limit to how many items can be stored in an array? My code looks in a directory of photos and writes the file names into the one-dimensional array. I just switched from my test folder to my actual photo folder - about 600 photos - and when I ran my script with var_dump enabled, my array only goes up to 128 items (0-127). I sure hope arrays aren't that wimpy?! Each photo is about 1-2MB in size, but since I am only storing the file name, I would expect that I could easily store thousands of elements in this array. What is going on?
Hi there, im really stuck here. I have a table of records that are selected from my db and put into an array and randomly chooses them, outputting the results . Code: [Select] <?php $count = 0; while ($row = mysql_fetch_assoc($Ships4)){ $array[$count]['ShipID'] = $row['ShipID']; $array[$count]['ShipName'] = $row['ShipName']; $array[$count]['Dclass'] = $row['Dclass']; $count++; $total4 = count($array); $random4 = rand(0,$total4 - 1); $random_ship4 = $array[$random4]; $ShipID4 = $random_ship4['ShipID']; echo $ShipID4;} This all works fine, however I want to limit the number of random outputs from a number in a record in a different table. So i added: Code: [Select] <?php if ($count < $row_dclasscorella['Dclass4_totla']){ ?> the number from the field is 2. The trouble is that it doesnt take any notice and outputs the total number a random number for each record. It seems its not limiting it. How can i get this to work please??? Code: [Select] $count = 0; if ($count < $row_dclasscorella['Dclass4_totla']){ while ($row = mysql_fetch_assoc($Ships4)){ $array[$count]['ShipID'] = $row['ShipID']; $array[$count]['ShipName'] = $row['ShipName']; $array[$count]['Dclass'] = $row['Dclass']; $count++; $total4 = count($array); $random4 = rand(0,$total4 - 1); $random_ship4 = $array[$random4]; $ShipID4 = $random_ship4['ShipID']; echo $ShipID4;}}?> Thank You |
|||||||