|
PHP - Help With Tables Search Resultssorting Table Columns Ascending And Descending
Hi. Im trying to get my table so I can click on username and it will display ascending or click again and it descends.
I want to be able to do this will all of them..Username Password IP Country Type Status E-mail Date start Date end .. If anyone can help me it would be great. I dont know PHP . I paid someone to make this for my and they binned it half way through the job taking my money... Thank you This is my code <?php if(session_is_registered('username')){ include("header.php"); ?> <div id="content_main" class="clearfix"> <div id="main_panel_container" class="left"> <div id="tabledata" class="section"> <h2 class="ico_mug">Users table</h2> <? if($_GET['type']=='delete'){ mysql_query("delete from users where id='".$_GET['id']."'"); ?><div id="success" class="info_div"><span class="ico_success">User deleted successful!</span></div><? } if($_GET['type']=='edit'){ if(isset($_POST['username'])){ mysql_query("UPDATE users SET username='".$_POST['username']."', password='".$_POST['password']."', ip='".$_POST['ip']."', country='".$_POST['country']."' WHERE id='".$_GET['id']."'"); } $row=mysql_fetch_array(mysql_query("SELECT * FROM users WHERE id='".$_GET['id']."'")); ?> <form action="" method="post" accept-charset="utf-8"> <fieldset> <label>Username:<input type="text" name="username" value="<?echo $row['username'];?>" size=60/></label> <label>Password:<input type="text" name="password" value="<?echo $row['password'];;?>" size=60/></label> <label>IP:<input type="text" name="ip" value="<?echo $row['ip'];;?>" size=60/></label> <label>Country:<input type="text" name="country" value="<?echo $row['country'];;?>" size=60/></label> <br /> <button>Submit</button> </fieldset> </form> <button onclick="location.href='index.php?action=users';">Back</button> <? } if($_GET['type']=='add'){ if(isset($_POST)){ $j=0; for($i=1; $i<=5; $i++){ if($_POST['username'.$i]!=''){ if(($_POST['username'.$i]!='') && ($_POST['password'.$i]!='') && ($_POST['ip'.$i]!='') && ($_POST['country'.$i]!='')) { if(($_POST['date_start'.$i]=='')||($_POST['date_end'.$i]=='')){ $date_start=0; $date_end=0; } else { $date_start=strtotime($_POST['date_start'.$i]); $date_end=strtotime($_POST['date_end'.$i]); } mysql_query("INSERT INTO users (username,password,ip,email,country,date_start,date_end) VALUES ('".$_POST['username'.$i]."','".$_POST['password'.$i]."','".$_POST['ip'.$i]."','".$_POST['email'.$i]."','".$_POST['country'.$i]."','".$date_start."','".$date_end."') "); $j++; } else { ?><div id="fail" class="info_div"><span class="ico_cancel">Fields on the same row can't be blank</span></div><? } } } if($j>0){ ?><div id="success" class="info_div"><span class="ico_success"><? echo $j;?> users have been added!</span></div><? } } ?> <fieldset> <i> Date format should be like 2000-05-25<br> Type: B for instant buy; S for subscription<br> Status: Y for active; N for inactive </i> </fieldset> <form action="" method="post" accept-charset="utf-8"> <table id="table"> <thead> <tr> <th>Username</th> <th>Password</th> <th>IP</th> <th>Email</th> <th>Country</th> <th>Type</th> <th>Status</th> <th>Date start</th> <th>Date end</th> </tr> </thead> <tbody> <? for ($i=1; $i<=5; $i++) { ?> <tr> <td> <input type="text" name="username<?echo $i;?>" size="10"> </td> <td> <input type="text" name="password<?echo $i;?>" size="10"> </td> <td> <input type="text" name="ip<?echo $i;?>" size="10"> </td> <td> <input type="text" name="email<?echo $i;?>" size="10"> </td> <td> <select name="country<?echo $i;?>"> <option value="UK">UK</option> <option value="US">US</option> </select> </td> <td> <select name="type<?echo $i;?>"> <option value="B" selected>B</option> <option value="S">S</option> </select> </td> <td> <select name="status<?echo $i;?>"> <option value="Y" selected>Y</option> <option value="N">N</option> </select> </td> <td> <input type="text" name="date_start<?echo $i;?>" size="10"> </td> <td> <input type="text" name="date_end<?echo $i;?>" size="10"> </td> </tr> <? } ?> <tr> <td><button>Submit</button> </td> </tr> </tbody> </table> </form> <br> <? } ?> <?php if($_GET['type']=='import') { if($_POST['import'] && $_FILES['flimport']['name']!='') { //print_r($_FILES); $fieldseparator = ","; $lineseparator = "\n"; $csvfile=$_FILES['flimport']['tmp_name']; $size = $_FILES['flimport']['size']; if(!$size) { exit; } $file = fopen($csvfile,"r"); $csvcontent = fread($file,$size); fclose($file); ////////////////////////////////////////////////////////////// $lines = 0; $queries = 0; $linearray = array(); foreach(split($lineseparator,$csvcontent) as $line) { $lines++; $line = trim($line," \t"); $line = str_replace("\r","",$line); $line = str_replace("'","\'",$line); $linearray = explode($fieldseparator,$line); $linemysql = implode("','",$linearray); $exist=0; if(is_numeric($linearray[0])) { $exist=mysql_num_rows(mysql_query("select id from users where username='".$linearray[2]."' or email='".$linearray[1]."'")); $query = "insert into users values('$linemysql');"; } else { $exist=mysql_num_rows(mysql_query("select id from users where username='".$linearray[1]."' or email='".$linearray[0]."'")); $query = "insert into users values('','$linemysql');"; } //echo $query."<br />"; if($exist==0) { @mysql_query($query); if(mysql_insert_id()) $queries++; } } echo '<div id="success" class="info_div"><span class="ico_success">'.$queries.' users added successfully</span></div>'; } ?> <form name="frmImport" action="" method="post" enctype="multipart/form-data"> <input type="hidden" value="1" name="import" /> <table id="table"> <tbody> <tr> <td><span style="font-size:10px; color:#333">Note: Please entry ms excel <strong>.CSV (Comma delimited)</strong> file only</span></td> </tr> <tr> <td><input type="file" accept="application/msexcel" name="flimport" /> </td> </tr> <tr> <td><button>Import Now</button></td> </tr> </tbody> </table> </form> <br /> <?php } ?> <button onclick="location.href='index.php?action=users&type=add';">Add users</button> <button onclick="location.href='index.php?action=users&type=import';" name="btnimport">Import users</button> <button onclick="location.href='export.php?datafrom=users';" name="btnexport">Export users</button> <table id="table"> <thead> <tr> <th>Actions</th> <th>Username</th> <th>Password</th> <th>IP</th> <th>Country</th> <th>Type</th> <th>Status</th> <th>E-mail</th> <th>Date start</th> <th>Date end</th> </tr> </thead> <tbody> <? if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; } $start_from = ($page-1) * 20; $query=mysql_query("SELECT * FROM users WHERE 1 LIMIT $start_from, 20"); while($row=mysql_fetch_array($query)){ ?> <tr> <td><a href="index.php?action=users&type=delete&id=<?echo $row['id'];?>"><img src="img/cancel.jpg" alt="cancel"/></a><a href="index.php?action=users&type=edit&id=<?echo $row['id'];?>"><img src="img/edit.jpg" alt="edit"/></a></td> <td><?echo $row['username'];?></td> <td><?echo $row['password'];?></td> <td><?echo $row['ip'];?></td> <td><?echo $row['country'];?></td> <td><?echo $row['type'];?></td> <td><?echo $row['status'];?></td> <td><?echo $row['email'];?></td> <td class="table_date"><?echo date("Y-m-d",$row['date_start']);?></td> <td class="table_date"><?echo date("Y-m-d",$row['date_end']);?></td> </tr> <? } ?> </tbody> </table> <? $rs_result = mysql_query("SELECT COUNT(id) FROM users"); $row = mysql_fetch_row($rs_result); $total_records = $row[0]; $total_pages = ceil($total_records / 20); ?> <div class="pagination"> <span class="pages">Page 1 of <?echo $total_pages;?></span> <?php for ($i=1; $i<=$total_pages; $i++) { if($page==$i){ ?><span class="current"><b><?echo $page;?></span></b><? } else { echo "<a href='index.php?action=users&type=view&page=".$i."'>".$i."</a> "; } } if($total_pages>1){ $next=$page+1; ?> <a href="index.php?action=users&type=view&page=<? echo $next;?>" >»</a> <? } ?> </div> </div> <!-- end #tabledata --> <? include("shortcuts.php"); ?> </div> <? ?> </div><!-- end #content_main --> </div><!-- end #content --> <? include("footer.php"); ?> <? } else { header( "Location: index.php?action=login" ); } ?> Similar TutorialsHow can I make this so it makes my table descending. http://www.ryanweekly.com/user/index.php?p=ryanweekly Code: [Select] <?PHP $maxRows_posting = 6; $pageNum_posting = 0; if (isset($_GET['pageNum_posting'])) { $pageNum_posting = $_GET['pageNum_posting']; } $startRow_posting = $pageNum_posting * $maxRows_posting; mysql_select_db($database_posting, $posting); $query_posting = "SELECT post, fromwho FROM post WHERE username = '$uasilly'"; $query_limit_posting = sprintf("%s LIMIT %d, %d", $query_posting, $startRow_posting, $maxRows_posting); $posting = mysql_query($query_limit_posting, $posting) or die(mysql_error()); $row_posting = mysql_fetch_assoc($posting); if (isset($_GET['totalRows_posting'])) { $totalRows_posting = $_GET['totalRows_posting']; } else { $all_posting = mysql_query($query_posting); $totalRows_posting = mysql_num_rows($all_posting); } $totalPages_posting = ceil($totalRows_posting/$maxRows_posting)-1; $queryString_posting = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_posting") == false && stristr($param, "totalRows_posting") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_posting = "&" . htmlentities(implode("&", $newParams)); } } $queryString_posting = sprintf("&totalRows_posting=%d%s", $totalRows_posting, $queryString_posting); $queryString_recordset2 = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_recordset2") == false && stristr($param, "totalRows_recordset2") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_recordset2 = "&" . htmlentities(implode("&", $newParams)); } } $queryString_recordset2 = sprintf("&totalRows_recordset2=%d%s", $totalRows_recordset2, $queryString_recordset2); $userida = $_REQUEST['p']; require_once('../Connections/loginsys.php'); ?> Hi all How do I modify the below code to search multiple tables in mySQL database? $query = "select * from store_items where description like \"%$trimmed%\" or title like \"%$trimmed%\" or dimensions like \"%$trimmed%\" order by id ASC"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); It is for a search function and I need it to search another table called 'about_text' and 'faq_text' Thanks Pete I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player? This is what I'm trying now: Code: [Select] /* CONNECTION VARIABLES */ $id = $_GET['id']; // get var from URL /* Get data. */ $sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'"; $result = mysql_query($sql); ?> <?php $alternate = "2"; while ($row = mysql_fetch_array($result)) { $field1 = $row["season"]; $field2 = $row["team"]; $field3 = $row["games"]; $field4 = $row["ave"]; $field5 = $row["slg"]; $field6 = $row["r"]; $field7 = $row["h"]; $field8 = $row["rbi"]; $field9 = $row["bb"]; $field10 = $row["k"]; $field11 = $row["hr"]; $field12 = $row["dbl"]; $field13 = $row["tpl"]; $field14 = $row["sb"]; $field15 = $row["obp"]; $field16 = $row["ops"]; if ($alternate == "1") { $color = "#ffffff"; $alternate = "2"; } else { $color = "#E4E4E4"; $alternate = "1"; } echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>"; } echo "</table>"; ?> Thank you in advance! I'm somewhat new to OOP PHP, and I'm still learning, but basically I want searchcolumns() to set which MySQL columns that it'll search inside but I don't know where to go from what I've already coded. Here's the basic input to be sent to my classes file: $getusers = new database; $getusers->search($_GET['search']); $getusers->searchcolumns('username','account','email'); And to put it simple, I need my $where variable to output this: $where .= "(username LIKE '%$search%' OR account LIKE '%$search%' OR email LIKE '%$search%')"; Except replacing the already set columns with the ones that I assigned in the searchcolumns() array, and then adding an OR string if there's more than 1 column set. Here's the code as I have it at the moment, for obvious reasons, it doesn't work how I want it to: // Search columns function searchcolumns($columns) { $this->column = explode(", ", $columns); } // Search results function search($search) { $search = ($_POST['search']); if (isset($_GET['search']) && strlen($_POST['search']) > 1) { $searchresult = explode(" ", $search); $where = "WHERE"; foreach ($searchresult as $key => $search) { $where .= "($this->column LIKE '%$search%')"; if ($key != (sizeof($searchresult) - 1)) $where .= " AND "; } $this->where = $where; } } Sorry if this has been somewhat confusing to explain, but hopefully someone understands what I'm trying to achieve. Hi,
How can I select multiple columns from two tables and run a search through multiple fields?
My tables a
t_persons (holds information about persons)
t_incidents (holds foreign keys from other tables including t_persons table)
What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors:
$query = "SELECT p.PersonID ,p.ImagePath ,p.FamilyName ,p.FirstName ,p.OtherNames ,p.Gender ,p.CountryID ,i.IncidentDate ,i.KeywordID ,i.IncidentCountryID ,i.StatusID FROM t_incidents AS i LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID WHERE FamilyName LIKE '%" . $likes . "%' AND FirstName LIKE '%" . $likes . "%' AND OtherNames LIKE '%" . $likes . "%' AND Gender LIKE '%" . $likes . "%' AND IncidentDate LIKE '%" . $likes . "%' AND KeywordID LIKE '%" . $likes . "%' AND IncidentCountryID LIKE '%" . $likes . "%' AND StatusID LIKE '%" . $likes . "%' ORDER BY PersonID DESC $pages->limit";Errors a Column 'IncidentDate' in where clause is ambiguous Column 'KeywordID' in where clause is ambiguous Column 'IncidentCountryID' in where clause is ambiguous Column 'StatusID' in where clause is ambiguousThese columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps. I will appreciate any better way to do this. Thanx. Joseph Attached Files Model - 3.png 76.6KB 0 downloads Im having problems with this code in my search button script. I get the following error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource Here's the code I use on my search form. Code: [Select] <form method="post" action="srch_all.php"><input type=text name='search' size=15 maxlength=255><br><input type=submit></form> and here's the code I'm using to perform the search query: Code: [Select] if ($search) // perform search only if a string was entered. { mysql_connect($host, $user, $pass) or die ("Problem connecting to Database"); $srch="%".mysql_real_escape_string($search)."%"; $query = "select * from database WHERE column1 LIKE '$srch' or column2 LIKE '$srch' or column3 LIKE '$srch' or column4 LIKE '$srch' ORDER BY column1, column2 DESC, column3 ASC"; $result = mysql_db_query("database_name", $query); if(mysql_num_rows($result)==0) { print "<h2>Your search returned 0 Results</h2>"; } else if ($result) { <restult data stuff here>.. Cheers I have a search set up to search a table for the text entered in a textbox, I have two columns in the table, one with the first name of people, and the second with their last names, I am wondering how I can search both, so for instance: I type in the search field: Roger Smith in the database it would look like: First_name-----|-----Last_name -------------------|------------------- Roger------------|-------Smith my current query is: Code: [Select] $query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' OR lname LIKE '%$find%'"); But if I type both parts of the name it doesn't return anything. works fine if I just search for "Roger" OR "Smith". I have now got a search engine to work, but it only show in normal text, could someone help me get into a table, I have a CSS file but cannot get the search page to print in my table.
<div id="main">
<table>
<thead>
<tr>
<th>ID</td>
<th>GAME</td>
<th>PLATFORM</td>
</tr>
<thead>
<?php
$output = NULL;
if(isset($_POST['submit'])) {
//Connect to database
$mysqli = NEW MySQLi ("localhost","root","","***.**");
$search = $mysqli->real_escape_string($_POST{'search'});
//Query the database
$resultSet = $mysqli->query("SELECT * FROM games WHERE GAME LIKE '$search%'");
if($resultSet->num_rows >0){
while($rows = $resultSet->fetch_assoc())
{
$id = $rows['ID'];
$gn = $rows['GAME'];
$pf = $rows['PLATFORM'];
$od = $rows['Owned'];?>
}
<tr>
<td><?php $output .= $id; ?></tr>;
<!-- Game: $gn<br />
Platform: $pf<br />
Owned: $od<br /><br />";-->
<form method="POST">
<input type="TEXT" name="search" />
<input type="SUBMIT" name="submit" value="Search" />
</form>
</table>
</body>
</html>
Also how can i use my connect file instead of having the connect to database in this file? I need some help on how to search in two different mysql tables, and then show the results sorted/ordered by the tables. Quote RESULTS: Tabel 1 # ... # ... Tabel 2 # ... # ... Thanks in advance! I have two tables in which different information of members are stored... table 1 stores the users main info, name, city, category, location table 2 stores extra info such as sub categories of items they have. Now i want to be able to search both tables for one value...say i type in red..i want to be able to search both tables at the same time to find the results and display them So far i'm stuck in figuring out how to do this, which is why i'm asking for help now. Hope someone can give me some guidance How do I search through table 1, 2, 3 where a=x and b=y and c=z? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330205.0 Hi,
I am trying to make a list of all countries listed in a database and the number of times they appear. But I am clearly doing something wrong as I am not able to list out the number of times that they appear.
This is what I have got so far:
<h4>Countries</h4>
<table><col width='150px'><col width='150px'>
<tr>
<th>
Country
</th>
<th>
Number of Occurences
</th>
</tr>
<?php
$location = DB::getInstance()->query("SELECT country, COUNT(country) as countCountry FROM users GROUP BY country ORDER BY count DESC");
foreach ($location->results() as $locations)
{
echo "<tr><td>";
echo $locations->country;
echo "</td><td>";
echo $locations->countCountry;
echo "</td></tr>";
}
?>
</table>
Can anyone point me in the right direction?
Hi, So I'm trying to make a car rental project, and I need to show cars of a specific type, available in a specific period. I'm currently using this query: SELECT * FROM car, reservations WHERE car.car_id = reservations.car AND car.type = 1 AND reservations.reserved_from >= "2011-12-22" AND reservations.reserved_to <= "2012-12-20"; So this returns all the cars of the type 1 reserved in 2011-12-22 - 2012-12-20 period. So know that I know which cars are unavailable, how would you go ahead and pick out what's left? I present videos on my site. The videos (their titles, descriptions, etc.) are stored in a MySQL database. I want visitors to be able to search my database for videos. My MySQL version is 5.5.40-36.1. The storage engine of my tables is INNODB. The two columns I want searched a title (varchar; unique index) description (text; no index) According to my research, the best way to allow users to search my database would be to use FULLTEXT, but my INNODB tables are incompatible. FULLTEXT is available for INNODB in MySQL 5.6.4 and above. I would rather not use LIKE '%...%', and I would rather not use a third party solution such as Sphinx because my database will never grow particularly large (I doubt it'll ever exceed 6,000–8,000 videos). What do you guys recommend? Is it okay to use LIKE with a database containing 6,000–8,000 items? Hello everybody,
I have been racking my brain trying to decipher something that should be really simple for a seasoned programmer. Newbieprogrammer
<html>
function display($randomNr)
So I have a database that is structured like this: https://imgur.com/a/DdyTqiE
I would like to loop through this table, and get a count of how many were 'is_no_show' and 'is_cancelled' per (unique) category with respect to 'scheduled_student_services.' How would I approach this? I know I probably need to do two loops but I'm not sure the PHP syntax for this. Any suggestions or tips would be helpful. Thank you for your time! Hello,
I have this very frustrating problem I'm trying to make a dynamic table with only 5 columns per row. So every 5 items, I need a new row. I have tried many different examples with no success. What is the best way to approach this? Here is what I am working with, this doesn't show what I have tried, just what I am working with at the moment which of course, just outputs it one column per row. http://www.mesquitew.../inc-legend.php
// Lets parse the data
$entries = simplexml_load_file($data);
if(count($entries)):
//Registering NameSpace
$entries->registerXPathNamespace('prefix', 'http://www.w3.org/2005/Atom');
$result = $entries->xpath("//prefix:entry");
foreach ($result as $entry):
$event = $entry->children("cap", true)->event;
// Set the alert colors for the legend
include ('../inc-NWR-alert-colors.php');
// Lets creat some styles for the list
$spanStyle = "background-color:{$alertColor};border:solid 1px #333;width:15px;height:10px;display:inline-block;'> </span><span style='font-size:12px;color:#555;";
$legend .= "<table>";
$legend .= "<tr>";
$legend .= "<td> <span style='$spanStyle'> $event</span></td>";
$legend .= "</tr>";
$legend .= "</table>";
endforeach;
endif;
echo $legend;
-Thanks!
When displaying data from a mysql table, what php code can I use to display the number of columns in said table? Like, say I'm displaying comments by users. I want to be able to put "displaying 'thismany' comments". Any suggestions? Hi, Mary Christmas My result of mysql database is LIMIT 8; now i have to retrieve and show this to two Horizontal columns As each columns 4 result. how to generate this Without tables!! only with DIV or UL/LI list . Thanks |
|||||||