|
PHP - Search Multiple Tables At Once
How do I search through table 1, 2, 3 where a=x and b=y and c=z?
Similar TutorialsHi 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, 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? 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. Hello, Does anyone know a tutorial I can follow to create my own search engine over the items I have in my SQL database? I find a lot of tutorials but they are all 'one word searches' which means if you type two words you will get all results that contain either word (too many hits). If the search engine tutorial displays result with AJAX even better. Thanks for help. df I have a form where a user can duplicate part of a form like below: I've named the fields with the HTML arrays (ie. "fieldName[]") and came up with this: //--> CONTROLLERS $system_controllers_qty = $_POST['system_controllers_qty']; $system_controllers_make = $_POST['system_controllers_make']; $system_controllers_model = $_POST['system_controllers_model']; $system_controllers_serial_no = $_POST['system_controllers_serial_no']; for ($i = 0; $i < count($system_controllers_qty); $i++) { echo "qty " . clean($system_controllers_qty[$i]) . "<br/>"; echo "make " . clean($system_controllers_make[$i]) . "<br/>"; echo "model " . clean($system_controllers_model[$i]) . "<br/>"; echo "serial_no " . clean($system_controllers_serial_no[$i]) . "<br/>"; } //--> CPUS $system_cpus_qty = $_POST['system_cpus_qty']; $system_cpus_model = $_POST['system_cpus_model']; $system_cpus_serial_no = $_POST['system_cpus_serial_no']; $system_cpus_speed = $_POST['system_cpus_speed']; for ($i = 0; $i < count($system_cpus_qty); $i++) { echo "qty " . clean($system_cpus_qty[$i]) . "<br/>"; echo "model " . clean($system_cpus_model[$i]) . "<br/>"; echo "serial_no " . clean($system_cpus_serial_no[$i]) . "<br/>"; echo "speed " . clean($system_cpus_speed[$i]) . "<br/>"; } //--> DISKS $system_disks_qty = $_POST['system_disks_qty']; $system_disks_make = $_POST['system_disks_make']; $system_disks_model_no = $_POST['system_disks_model_no']; $system_disks_size = $_POST['system_disks_size']; $system_disks_serial_no = $_POST['system_disks_serial_no']; for ($i = 0; $i < count($system_disks_qty); $i++) { echo "qty " . clean($system_disks_qty[$i]) . "<br/>"; echo "make " . clean($system_disks_make[$i]) . "<br/>"; echo "model_no " . clean($system_disks_model_no[$i]) . "<br/>"; echo "size " . clean($system_disks_size[$i]) . "<br/>"; echo "serial_no " . clean($system_disks_serial_no[$i]) . "<br/>"; } //--> MEMORY $system_memory_qty = $_POST['system_memory_qty']; $system_memory_serial_no = $_POST['system_memory_serial_no']; $system_memory_manf = $_POST['system_memory_manf']; $system_memory_part_no = $_POST['system_memory_part_no']; $system_memory_size = $_POST['system_memory_size']; for ($i = 0; $i < count($system_memory_qty); $i++) { echo "qty " . clean($system_memory_qty[$i]) . "<br/>"; echo "serial " . clean($system_memory_serial_no[$i]) . "<br/>"; echo "manf " . clean($system_memory_manf[$i]) . "<br/>"; echo "part_no " . clean($system_memory_part_no[$i]) . "<br/>"; echo "size " . clean($system_memory_size[$i]) . "<br/>"; } This just outputs all the fields I post at the moment. Say I have 3 different disk types, it's going to loop 3 times. Is it possible to make this all into 1 giant query or am I better off doing each query separately? Each section is a different table. I have a search where I want to be able to search a string of words. The search is going to be looking in 2 different table joined by a left outer join. The tables are "quotes" and "categories". $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; The above mysql statement works and returns values..BUT if say, I search "john" and "funny", and a quote is posted by the user "john", but has a category of "funny" it will output the same quote twice. I was wondering if there was a way to see if a quote has either 1 term or both terms, if so display that quote but only display it once. Below is what the query is outputting. [100] => Array ( [id] => 100 [username] => John [quote] => new test quote blah blah [by] => John [voteup] => 0 [votedown] => 0 [servtime] => 2010-12-02 @ 16:27:03 [label] => Array ( [0] => Historic [1] => Serious [2] => Funny ) ) Here is the code in full. //// $sword = explode(" ",$search); foreach($sword as $sterm){ $srch="%".$sterm."%"; echo"$srch<br />"; $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; $result=mysql_query($sql); while($row=mysql_fetch_object($result)){ $quote[$row->id]['id'] = $row->id; $quote[$row->id]['username'] = $row->username; $quote[$row->id]['quote'] = $row->quote; $quote[$row->id]['by'] = $row->by; $quote[$row->id]['voteup'] = $row->voteup; $quote[$row->id]['votedown'] = $row->votedown; $quote[$row->id]['servtime'] = $row->servtime; $quote[$row->id]['label'][] = $row->label; } echo"<pre>"; print_r($quote); echo"</pre>"; I don't think this is the fastest way of doing this, as it loops for each item in the db, per each keyword that is search. Any help would be great!! -BaSk I am trying to export multiple csv files to download from 2 separate tables in my database. Some background: I have a web app that links to a phpmyadmin database. There are 2 tables in the database (entering and exiting). These tables hold the phone inventory information of employees currently entering or exiting the organization. The fields in my tables a location, firstname, lastname, username, extension, mac, type What I am trying to do is export the data in these 2 tables to CSV (which I have working now) but I need to have multiple CSVs for each. For example, for my exiting table, I need to have one CSV export all the fields in the table and I also need another CSV to export just the location and username field. I have a simple html form with a submit button which is currently working now: <form action="exportexiting.php" method="POST" style="width: 456px; height: 157px"> <div> <fieldset> <legend style="width: 102px; height: 25px"><strong>Entering CSV:</strong></legend> <input name="Export" type="submit" id="Export" value="Export"> </fieldset><br/> </div> </form> This links to my exportexiting.php file: <?php $host = 'localhost'; $user = 'root'; $pass = 'xxxx'; $db = 'PhoneInventory'; $table = 'exiting'; // Connect to the database $link = mysql_connect($host, $user, $pass); mysql_select_db($db); require 'exportcsv.inc.php'; exportMysqlToCsv($table); ?> Then to exportcsv.inc.php: <?php function exportMysqlToCsv($table,$filename = 'export.csv') { $csv_terminated = "\n"; $csv_separator = ","; $csv_enclosed = ''; $csv_escaped = "\\"; $sql_query = "select * from $table"; // Gets the data from the database $result = mysql_query($sql_query); $fields_cnt = mysql_num_fields($result); $schema_insert = ''; for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out)); // Output to browser with appropriate mime type, you choose header("Content-type: text/x-csv"); //header("Content-type: text/csv"); //header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); echo $out; exit; } ?> Again, this is working perfectly for only exporting all the data from the exiting table into one CSV file. My question is, how can I make my one submit button export the 2 CSV files that I need? Thanks for the help... does anyone have a code todo this? a snippet or soemthing ive tried a few diffrent ones ive seen but nothing that works.. it takes whats submited in search, then takes the words, breaks them up and creates a query string with all the diffrent feilds... here is what phpadmin produces when i search SELECT * FROM `breedclads`.`activeamahorse` WHERE (`id_entered` LIKE '%green%' OR `uuid` LIKE '%green%' OR `ama_hr_coat` LIKE '%green%' OR `breedable` LIKE '%green%' OR `ama_hr_sex` LIKE '%green%' OR `ama_hr_age` LIKE '%green%' OR `date_entered` LIKE '%green%' OR `ama_hr_eyes` LIKE '%green%' OR `ama_hr_eyes_type` LIKE '%green%' OR `ama_hr_mane` LIKE '%green%' OR `ama_hr_tail` LIKE '%green%' OR `ama_hr_luster_coat` LIKE '%green%' OR `ama_hr_luster_hair` LIKE '%green%' OR `ama_hr_gleam_coat` LIKE '%green%' OR `ama_hr_gleam_hair` LIKE '%green%' OR `ama_hr_parents` LIKE '%green%' OR `ama_hr_sale_type` LIKE '%green%' OR `ama_hr_sale_price` LIKE '%green%' OR `ama_hr_location` LIKE '%green%' OR `auction_date` LIKE '%green%' OR `picture_type` LIKE '%green%' OR `picture_name` LIKE '%green%' OR `picture_size` LIKE '%green%' OR `picture_content` LIKE '%green%' OR `ama_hr_coat_type` LIKE '%green%' OR `ama_hr_coat_gloom` LIKE '%green%' OR `ama_hr_hair_gloom` LIKE '%green%' OR `ama_hr_slurl` LIKE '%green%' OR `class_link_key` LIKE '%green%') AND (`id_entered` LIKE '%albino%' OR `uuid` LIKE '%albino%' OR `ama_hr_coat` LIKE '%albino%' OR `breedable` LIKE '%albino%' OR `ama_hr_sex` LIKE '%albino%' OR `ama_hr_age` LIKE '%albino%' OR `date_entered` LIKE '%albino%' OR `ama_hr_eyes` LIKE '%albino%' OR `ama_hr_eyes_type` LIKE '%albino%' OR `ama_hr_mane` LIKE '%albino%' OR `ama_hr_tail` LIKE '%albino%' OR `ama_hr_luster_coat` LIKE '%albino%' OR `ama_hr_luster_hair` LIKE '%albino%' OR `ama_hr_gleam_coat` LIKE '%albino%' OR `ama_hr_gleam_hair` LIKE '%albino%' OR `ama_hr_parents` LIKE '%albino%' OR `ama_hr_sale_type` LIKE '%albino%' OR `ama_hr_sale_price` LIKE '%albino%' OR `ama_hr_location` LIKE '%albino%' OR `auction_date` LIKE '%albino%' OR `picture_type` LIKE '%albino%' OR `picture_name` LIKE '%albino%' OR `picture_size` LIKE '%albino%' OR `picture_content` LIKE '%albino%' OR `ama_hr_coat_type` LIKE '%albino%' OR `ama_hr_coat_gloom` LIKE '%albino%' OR `ama_hr_hair_gloom` LIKE '%albino%' OR `ama_hr_slurl` LIKE '%albino%' OR `class_link_key` LIKE '%albino%') AND (`id_entered` LIKE '%69%' OR `uuid` LIKE '%69%' OR `ama_hr_coat` LIKE '%69%' OR `breedable` LIKE '%69%' OR `ama_hr_sex` LIKE '%69%' OR `ama_hr_age` LIKE '%69%' OR `date_entered` LIKE '%69%' OR `ama_hr_eyes` LIKE '%69%' OR `ama_hr_eyes_type` LIKE '%69%' OR `ama_hr_mane` LIKE '%69%' OR `ama_hr_tail` LIKE '%69%' OR `ama_hr_luster_coat` LIKE '%69%' OR `ama_hr_luster_hair` LIKE '%69%' OR `ama_hr_gleam_coat` LIKE '%69%' OR `ama_hr_gleam_hair` LIKE '%69%' OR `ama_hr_parents` LIKE '%69%' OR `ama_hr_sale_type` LIKE '%69%' OR `ama_hr_sale_price` LIKE '%69%' OR `ama_hr_location` LIKE '%69%' OR `auction_date` LIKE '%69%' OR `picture_type` LIKE '%69%' OR `picture_name` LIKE '%69%' OR `picture_size` LIKE '%69%' OR `picture_content` LIKE '%69%' OR `ama_hr_coat_type` LIKE '%69%' OR `ama_hr_coat_gloom` LIKE '%69%' OR `ama_hr_hair_gloom` LIKE '%69%' OR `ama_hr_slurl` LIKE '%69%' OR `class_link_key` LIKE '%69%') i need this to be made when people search with mutliple words as you can see it searchs each feilds for the green then each feild for albino, so on.. but i wont ever know what there searching for Please help me, 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? 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! 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 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? 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! 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" ); } ?> I am working on a asset project to insert, delete and view assets (items) that i owe. I am having trouble in the insert part. i have four tables department (DeptID, DeptName) DeptID is the primary key in this table. assetcategory (AssetCatID, AssestCategory) AssetCatID is the primary key in this table. asset (AssetID, AssetDescription, EmpID, AssetCatID, DeptID, Model, Maker, SerialNUm, DateAguired) AssetID is the primary key here. Employee (EmpID, FirstName, LastName) EmpID is the key here. and this is the code i have to insert using php. Code: [Select] <?php if(isset($_POST['submit'])){ $FirstName = mysql_real_escape_string($_POST["FirstName"]); $LastName = mysql_real_escape_string($_POST["LastName"]); $DeptName = mysql_real_escape_string($_POST["DeptName"]); $AssetCategory = mysql_real_escape_string($_POST["AssetCategory"]); $Model = mysql_real_escape_string($_POST["Model"]); $Maker = mysql_real_escape_string($_POST["Maker"]); $SerialNum = mysql_real_escape_string($_POST["SerialNum"]); $DateAguired = mysql_real_escape_string($_POST["DateAguired"]); $AssetDescription = mysql_real_escape_string($_POST["AssetDescription"]); $AssetCatID = mysql_real_escape_string($_POST["AssetCatID"]); $AssetID = mysql_real_escape_string($_POST["AssetID"]); $EmpID = mysql_real_escape_string($_POST["EmpID"]); $DeptID = mysql_real_escape_string($_POST["DeptID"]); if(empty($FirstName) || empty($LastName) || empty($DeptName) || empty($AssetCategory) || empty($Model) || empty($Maker) || empty($SerialNum) || empty($DateAguired) || empty($AssetDescription)) { print "Please feel in all fields"; } else { ///insert into the department table.......... $query1= "INSERT INTO department VALUES (null,'{$DeptName}')"; $result1 = mysql_query($query1) or die(mysql_error()); $DeptID = mysql_insert_id(); //insert into the assetcategory table $query2= "INSERT INTO assetcategory VALUES (null,'{$AssetCategory}')"; $result2 = mysql_query($query2) or die(mysql_error()); $AssetCatID = mysql_insert_id(); //insert into the assets table $query3= "INSERT INTO assets VALUES (null,'{$AssetDescription}', '{$EmpID}','{$AssetCatID}','{$DeptID}','{$Model}','{$Maker}','{$SerialNum}','{$DateAguired}')"; $result3 = mysql_query($query3) or die(mysql_error()); $AssetCatID = mysql_insert_id(); print $query1; } } else { ?> the page is displaying this error: "Column 'AssetCatID' cannot be null" I would need help in inserting into all table required data. any help? hope i am making sense I'm having a hard time grasping how to select data from several tables. Here's the code Code: [Select] <?php require_once "config.php"; $gamedate = $_SESSION['date']; echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />"; $umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name'); $dbc = mysql_pconnect($host, $username, $password); mysql_select_db($db,$dbc); foreach($umpirelist as $data) { //now get stuff from a table $sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; $rs = mysql_query($sql,$dbc); $matches = 0; while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "$row[$data]<br />"; } } ?> The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game. it does have a unique "row_number" column as well, but didn't think I'd need it in this select. The table VACATION has the date, and umpire's name that is on vacation. The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info. I'm getting this error: Quote Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152 This is line 152 Quote while ($row = mysql_fetch_assoc($rs)) { Can someone please point me in the right direction as to where I'm going wrong? Thanks! Need help in updating the tables I have array of data coming from a form which I'm inserting in my DB. I have 5 tables product filter product_filter heater product_heater Im able to put the data in the "product", "filter" & "heater" tables but i dont know how to put data inside the "product_filter" & "product_heater" table. Any help or direction to any tutorails is appreciated. My Tables structu product id int(5) product text cost text details text filter id int(5) filter text imgpath text product_filter id int(5) id_product int(5) id_filter int(5) heater id int(5) heater text imgpath text product_heater id int(5) id_product int(5) id_heater int(5) Code: [Select] // Product data Update $name = mysql_real_escape_string($_POST['product']); $cost = mysql_real_escape_string($_POST['cost']); $details = mysql_real_escape_string($_POST['details']); $sql_title = "INSERT INTO product ( id , product , cost , details , ) VALUES ( NULL , '$name' , '$cost' , '$details')"; if (!mysql_query($sql_title,$con)) { die('Error: ' . mysql_error()); } echo "records for product added<br />"; // Filter update // This is the array which is coming from the form /* Array ( [0] => ehiem [1] => Hagan [2] => Rena [3] => jobo ) Array ( [0] => img1.jpg [1] => img2.jpg [2] => img3.jpg [3] => img4.jpg ) */ $filtername = mysql_real_escape_string($filtername); $filterimgpath = mysql_real_escape_string($filterimg); $combined_array = array_combine($filtername, $filterimgpath); $values = array(); foreach ($combined_array as $filtername => $filterimgpath) { $values[] = "('$filtername', '$filterimgpath')"; } $sql = "INSERT INTO filter (filter , imgpath) VALUES " . implode(', ', $values); //echo $lastid = mysql_insert_id()."<br />"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "records added<br />"; //Product Filter Update table // This is where Im stuck. Not able to even think of anything.... // heater update // This is the array which is coming from the form /* Array ( [0] => ehiem [1] => Dolphin [2] => Rena [3] => jobo ) Array ( [0] => img1.jpg [1] => img2.jpg [2] => img3.jpg [3] => img4.jpg ) */ $heatername = mysql_real_escape_string($heatername); $heaterimgpath = mysql_real_escape_string($heaterimg); $combined_array = array_combine($heatername, $heaterimgpath); $values = array(); foreach ($combined_array as $heatername => $heaterimgpath) { $values[] = "('$heatername', '$heaterimgpath')"; } $sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values); //echo $lastid = mysql_insert_id()."<br />"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "records added<br />"; //Product heater Update table // This is where Im stuck. Not able to even think of anything.... I'm not sure if this belongs in the PHP or SQL sections or both but- Is it possible to insert form data (one record) into 2 different tables when I click submit? As is, I have 2 distinct log-in scripts each linked to 2 separate databases . I want to merge them into one log-in form that populates both tables(in different dbs). I don't want my visitors to log into 2 different forms. Can one form's action attribute send the POST data to two different processing scripts? and can the input from a text box be sent to 2 different fields? I don't have any code, I just wanted to know if it can be done or point me to a sample that I can work from. I hope this isn't a stupid question. I have written a fairly large site using php and SQL. The site has multiple features (how tos, classifieds, photos, videos, ect) with each section's data housed in its own table. Now I would like to have one highlight from each section appear in boxes on the index page simutaniously. (basically returning one random item from each table when the page is loaded) I know how to call each table individually with sql = mysql_query, however, I fear that calling the database that many times over and over in one page load could be problematic if the site begins to get a lot of traffic. Am I correct in worring about overloading the server and is there a better way to do this?
|
|||||||