|
PHP - General Question - Search/subsearch/temporary Tables
Bear with me, as I'm still quite new at PHP.
I'm trying to figure out the best way to build a search function for a database that will eventually be quite large. The path of the search I'm building is to go from a straight-forward javascript and html search page, to a paginated search result. Then the user can narrow down those query results based on a new search of their results. In trying to get the pagination to work across several pages(unsuccessfuly, I might add), I've been learning about sessions and temporary tables. But, because the database will be very large, I'm wondering if they are the wrong way. Sessions have a time limit, and temporary tables delete once the connection is closed. Is it possible/feasible to build a permanent table that puts in the search variables, with a unique id and use this to manage the searches, pagination and all that? Then, at some point in the process, I can put code to delete the corresponding row (or even make it a saveable search). Maybe somebody sees where I'm going with this and can describe it better than me? I'm just thinking off the cuff at the moment. Maybe there's some terminology that will help me find a tutorial. Any bit helps. thanks! Similar TutorialsI'm ready for the next step on my site, which is learning how to filter results down with a second search. The scenario is: A viewer searches for a plant by 4 variables to describe the leaf. After submit, they go to the results page, with 200 paginated results. (This is where I've gotten to) Now, on that same page, they choose to further narrow their results by searching those 200 results for plants that have purple flowers. Maybe they get 40 results and can use another variable to narrow further - and so on. What's the best way to set this up? I've been looking into temporary tables, but it seems to be a challenge because the table deletes the moment they leave that page or close the connection? Sessions seem like another possibility, but I read there's security issues. What's the best way to set this up? Just re-query the database with the entire set of variables from both the first and second searches? is it quicker to build some type of permanent table with a timestamp that I can use to delete it after the user has gone away? i've been able to load up a csv file and display it as a html table but what i'm having trouble with is sorting it out with headings a to z but what i am trying to do is have 4 columns like this. Surgestions about how to do what im after i was hoping i could span the say A across all columns and centering them in the future also but thats not really the problem mainly the getting the array of the csv file and code right iguess. any help would be appreciated Code: [Select] <table> <tr> <th>A</th> </tr> <tr> <td>Apple1</td> <td>Apple2</td> <td>Apple3</td> <td>Apple4</td> <td>Apple5</td> <td>Apple6</td> </tr> <tr> <th>B</th> </tr> <td>Banana1</td> <td>Banana2</td> <td>Banana3</td> <td>Banana4</td> <td>Banana5</td> <td>Banana6</td> <tr> <th>C</th> </tr> <td>Cold1</td> <td>Cold2</td> <td>Cold3</td> <td>Cold4</td> <td>Cold5</td> <td>Cold6</td> </table> This is the code for inputting of the csv file i also have some other code i was wanting to use for the sorting of the array data from the csv file i'll past below.... Quote <?php $file = "widgets.csv"; $delimiter = ","; $enclosure = '"'; $column = array("", "", "", ""); @$fp = fopen($file, "r") or die("Could not open file for reading"); while (!feof($fp)) { $tmpstr = fgets($fp, 100); $line[] = preg_replace("/r/", "", $tmpstr); } for ($i=0; $i < count($line); $i++) { $line[$i] = explode($delimiter, $line[$i]); } ?> <html> <head> <title>Albert's Delimited Text to HTML Table Converter</title> <link href="css/style.css" rel="stylesheet" type="text/css" /> </head> <body> <table border="1" cellpadding="5" cellspacing="0"> <tr> <?php for ($i=0; $i<count($column); $i++) echo "<th>".$column[$i]."</th>"; ?> </tr> <?php for ($i=0; $i < count($line); $i++) { echo "<tr>"; for ($j=0; $j < count($line[$i]); $j++) { echo "<td>".$line[$i][$j]."</td>"; } echo "</tr>"; } fclose($fp); ?> </table> </body> </html> Heres the code for the sorting of the table data from the array Quote <?php echo "QUERY_STRING is ".$_SERVER['QUERY_STRING']."<p>"; $rev=1; $sortby=0; if(isset($_GET['sortby']))$sortby = $_GET['sortby']; if(isset($_GET['rev']))$rev = $_GET['rev']; //Open and read CSV file example has four columns $i=-1; $ff=fopen('widgets2.csv','r') or die("Can't open file"); while(!feof($ff)){ $i++; $Data[$i]=fgetcsv($ff,1024); } //Make columns sortable for each sortable column foreach ($Data as $key => $row) { $One[$key] = $row[0]; // could be $row['Colname'] $Two[$key] = $row[1]; //numeric example $Three[$key] = $row[2]; $Four[$key] = $row[3];} //numeric example //Case Statements for Sorting switch ($sortby){ case "0": if($rev=="1")array_multisort($One, SORT_NUMERIC, $Data); //SORT_NUMERIC optional else array_multisort($One, SORT_NUMERIC, SORT_DESC,$Data); $rev=$rev*-1; break; case "1": if($rev=="1")array_multisort($Two, $Data); else array_multisort($Two, SORT_DESC, $Data); $rev=$rev*-1; break; case "2": if($rev=="1")array_multisort($Three, SORT_NUMERIC, $Data); else array_multisort($Three, SORT_NUMERIC, SORT_DESC,$Data); $rev=$rev*-1; break; case "3": if($rev=="1")array_multisort($Four, $Data); else array_multisort($Four, SORT_DESC, $Data); $rev=$rev*-1; break;} echo ("<table border=2>"); //$rev is reverse variable echo ("<th><a href=\"SortTable.php?sortby=0&rev=$rev\" title=\"Click to Sort/Reverse sort\">One</a></th>"); echo ("<th><a href=\"SortTable.php?sortby=1&rev=$rev\" title=\"Click to Sort/Reverse sort\">Two</a></th>"); echo ("<th><a href=\"SortTable.php?sortby=2&rev=$rev\" title=\"Click to Sort/Reverse sort\">Three</a></th>"); echo ("<th><a href=\"SortTable.php?sortby=3&rev=$rev\" title=\"Click to Sort/Reverse sort\">Four</a></th>"); $i=0; foreach($Data as $NewDat){ if($NewDat[0]!=""){ //error trap $str="<tr>"; foreach($NewDat as $field)$str.="<td>$field</td>"; $str.="</td>\n"; echo $str;}} fclose($ff); echo "</table>"; This topic has been moved to Ajax Help. http://www.phpfreaks.com/forums/index.php?topic=321915.0 Hi, I am really plugging into how to write functions in PHP. But I was going to delve into a user management program and try to create it, but dont want to be the older version of what I was before if you like, where I just type in for the sake of typing in code so I thought I would question what their doing. But a peice of code, a very small snippet, this came up: @mysql_connect What does this actually mean with the @ sign infront of the mysql_connect function? Seen this a few times but just never appreciated what the at sign means, any help is wonderfully appreciated of course. Thanks, Jeremy I have a general questions. 1. I have a list of products. 2. I have a list of each of the 50 states. 3. I have a list of "professions". I need to setup something called "State Verbiage". However, this is going to be on a per product, per state, per profession level. So each profession is mapped up to each state and product. So product #1, in State #4, for profession #2 would have verbiage. Any combination of the three would have different verbiage. Any advice on the easiest way to set this up, would be appreciated. Hi there just wanted to ask you a general question regarding referenced variables and functions in PHP. I have this code: function theRefFunction(&$var){ $var = $var +1; return $var; } $a = 50; echo $thisvalue = theRefFunction($a); Just for learning purposes, as allot of times beginning PHP seriously developers who want to improve on memory consumption in PHP find this tricky, is there any point in using an example like this? I.e. would I really need the &$var as the parameter for the function called 'theRefFunction()'? Just wondered thats all, if not what would be a better way for really using it? It's just so I can go onto maybe doing a loop in it and setting it as a real example like working out tax and stuff like that, just for learning purposes, won't yet be using it. Just wanted to build up as I said earlier a library of things I have done and make maybe my own tutorial site. I look forward to any replies, Jeremy. Sorry for the non code lingo, but I am wondering if I can pull a word from an array and use it to get a value from another array.
I am making a simple dodgeball game engine. I want the coaches strategy to pick a certain attribute to base who to throw to. Here is the team array:
//silverbacks 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 Hi All: I have general question on php... if I have an "includes" folder, and on IE somebody goes directly to it, like: http://www.site.com/includes on IE, they can see the folder-tree- directory. So, files like config.php, or mysql.php could be visible. My question is, is this safe?? couldnt people then just drag-copy the file over into their system? or somehow open it, view its contents? I dragged-copied a .php file into my desktop, but nothing shows up when I open in in notepad. As opposed to opening another file that was also .php but which only had javascript code in it, and the javascipt did show up. So, I take it, it should be ok?? as it seems like it works as if the .php page actually had been loaded on a web-browser... doesnt show the php code... well, appreciate comments, feedback on this... what woudl be the best way to protect files like this? htaccess-protect? or is this not something to be overly concerned about?? From what I can tell looks like a lot of site are setup this way... so must be ok... ha? Thanks! 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? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330205.0 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? 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! 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? Not sure if this topic goes in here, it is related to PHP but also MySql, so if i'm on the wrong board sorry! What i'm trying to do is search for a keyword in 5 different tables and return the keyword ID from the table that its in The tables i'm trying to search are as follow location state county region continent The "location" table has all the locations i.e cities and each row has the following columns: id | continent_id | country_id | state_id | region_id | city_name The "state" table is set the the following: id | name "county" table : id | name "region" table: id | region and "continent" table id | name The way it works is the can search for any city or state or county or region or continent and ideally it should look into the five different tables and return the id of that table. So if the use searches for United States it will look for United States in all five tables, obviously it would find it in the "country" table so it should return that "id". The results are returned in "json format" Below is the code i have: Code: [Select] <? $input = $_GET['keyword']; $data = array(); /* In this query i'm attempting to search in all databases, but i'm not sure if i'm doing this right. I'm not getting any results so i know something is wrong just don't know how to write it. */ $query = mysql_query("SELECT * FROM locations JOIN states ON states.id = locations.state_id JOIN countries ON countries.id=locations.country_id JOIN regions ON regions.region_id = locations.region_id JOIN continents ON continents.id=locations.continent_id WHERE name LIKE '$input%' OR state LIKE '$input%' OR region LIKE '$input%' OR country LIKE '$input%' OR continent LIKE '$input%'") or die(mysql_error()); /* Here the values are added to to the $json array. The "value" should be the "id" from the table that the keyword matched. The 'name' Should be the name of the actual keyword. Again if they search for United States the "id" will come from the "countries" table and the "value" would come from the "countries" table as the name */ while ($row = mysql_fetch_assoc($query)) { $json = array(); $json['value'] = $row['id']; $json['name'] = $row['name']; $data[] = $json; } header("Content-type: application/json"); echo json_encode($data); ?> Any help would he be appreciated, i don't want people to do it for me, but rather just guide me a little bit. 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" ); } ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=345265.0 My users table on my forum is what gets checked everytime a user refreshes to authentic them.
When killing a monster, I want users to be able to grab items and it will insert them into their inventory. I have this part done already. But I want them to have a plethora of loot available that drops from a mob, and once they click the specific item it will then be inserted into their inventory (server database).
I'm storing the Temporary Item Data in a PHP session variable. Once they kill a monster, the Tempory Item Data variable get's filled with the specific loot and then the user will have the option to choose what items they want to go into their inventory.
My problem is, if they open the game in a new browser they will get a new session id. Since I'm authenticating them through my users table, can I just make a new column called session_id and just use php's session_id() before every session start so no matter which browser they're on they will have the same session right?
You might think, well why dont you just store the temporary item data in a mysql field or rows instead? I want to try to minimize mysql usage as much as possible, as players will be CLICKING a lot to kill mobs, it is most likely very mysql demanding as well and I want to be intuitive about it. I just want to use temporary session data for the loot. Then once the user clicks an item they want, it is EXTRACTED from their tempory item data variable, then I will use MYSQL to insert those items into their inventory.
Is this a fair and intuitive way to do temporary data for item loot? For example, in action RPG's like Path of Exile you kill a group of mobs and you see a shit ton of loot on the floor. (I imagine that loot is just temporary waiting for someone to pick it up right?) Once you do pick it up, mysql is then called to save it right? That's the same logic I have with my web based game. Is using a session variable to store that temporay loot.
Is this an intuitive way to do this, or are there other ways?
Edited by Monkuar, 24 November 2014 - 01:24 AM. |
|||||||