PHP - Searching Multiple Tables For Pagination

First off, hello all!

I want to improve our website's search but unfortunately don't know enough to get there!

I'm not a PHP programmer but I'm generally able to tweak things... 

Our search page has 3 options - All words, Any word, Exact. 

I want to concentrate on the default "All words" which is the default parameter.

The code is essentially as follows -

Code: [Select]
$gotcriteria=TRUE;
$Xstext = mysql_escape_string($stext);
$aText = split(" ",$Xstext);
$aFields[0]="products.pId";
$aFields[1]="products.pName";
$aFields[2]="products.pDescription";
$aFields[3]="products.pLongDescription";
if($stype=="exact")
$sSQL .= "AND (products.pId LIKE '%" . $Xstext . "%' OR ".getlangid("pName",1)." LIKE '%" . $Xstext . "%' OR ".getlangid("pDescription",2)." LIKE '%" . $Xstext . "%' OR ".getlangid("pLongDescription",4)." LIKE '%" . $Xstext . "%') ";
else{
$sJoin="AND ";
if($stype=="any") $sJoin="OR ";
$sSQL .= "AND (";
for($index=0;$index<=3;$index++){
//$sSQL .= "(";
$rowcounter=0;
$arrelms=count($aText);
foreach($aText as $theopt){
if(is_array($theopt))$theopt=$theopt[0];
$sSQL .= $aFields[$index] . " LIKE '%" . $theopt . "%' ";
if(++$rowcounter < $arrelms) $sSQL .= $sJoin;
}
//$sSQL .= ") ";
if($index < 3) $sSQL .= "OR ";
}
$sSQL .= ") ";
}

Here's the important bit - as far as I can see, the search looks at each of the 4 fields in the array individually?  So if the search text is "RED APPLE", to get a result for the "All Words" option will require that both "RED" AND "APPLE" will need to be present in one of the searched fields...?

What I want to achieve is that if the product name is "RED FRUIT" and the description mentions "APPLE", then an "All words" search for "RED APPLE" will pick this up.

I'd guess that the easiest way would be to string the table fields together for searching but I don't know how to do this as a query... now I'm thinking I'll get flamed for not posting this in the MySQL forum too!! 

Thanks in advance for any help!

I’m writing a small application that allows me/users to add their own inputs so they can store their own data.  It’s kind of like, a user defined database.  Think of a super simple “filemaker” kind of application.    The way I’ve set this up is like this:

Table: item
fields: item_id, user_id

Table item_attribute
Fields: item_attribute_id, item_id, name, value

After my query I end up with stuff like this:

$data = array(
            0=>array(
                        '123'=>array(
                                    'name'=>'Year',
                                    'value'=>'1985'
                        ),
                        '7'=>array(
                                    'name'=>'Title',
                                    'value'=>'Title For 1985'
                        ),
                        '25'=>array(
                                    'name'=>'Length',
                                    'value'=>'60'
                        )
            ),
            1=>array(
                        '123'=>array(
                                    'name'=>'Year',
                                    'value'=>'1990'
                        ),
                        '7'=>array(
                                    'name'=>'Title',
                                    'value'=>'Title For 1990'
                        ),
                        '25'=>array(
                                    'name'=>'Length',
                                    'value'=>'44'
                        )
            ),
            2=>array(
                        '123'=>array(
                                    'name'=>'Year',
                                    'value'=>'1965'
                        ),
                        '7'=>array(
                                    'name'=>'Title',
                                    'value'=>'Title For 1965'
                        ),
                        '25'=>array(
                                    'name'=>'Length',
                                    'value'=>'122'
                        )
            )
);

This seems to work great as there is no limit to what a user can add - it's working like I would like it to...   But now that I’m trying to add sorting/filtering/pagination to results things have gotten extremely difficult on the back end.   The only thing I can think of is to pull all the data and then use php algorithms to sort/filter/paginate before sending over to web page.  I can see this getting slow if I eventually have to pull 1000s of records.     Does anyone have advice in these matters?  Should I rethink my db design?  Can the results be used to create a temporary table and then sort on it?  (don'n know how or if this is even possible) I need some help   Thanks.
Edited by fivestringsurf, 16 November 2014 - 12:28 PM.

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...

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.

Hi

I have a results page from a  MySQL query and I have implemented pagination to present data in chunks of 25 per page in a table view (each row => 1 entry).

My problem is that next to each data row, I have a selection checkbox, and if the user clicks on it, he can download the selected entries. This thing worked OK before I employed paginated view, when I presented all data in one page. Now, if I select e.g. 2 entries in page 1, then I jump to page 12 and select another 2 entries, if I click on the "Retrieve" button, I get only the last 2 entries (from the last page visited).

How can I solve this problem? With JS? Can I use PHP only?

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!

Hi,

I'm very new to PHP and have managed to get through most of the problems I've encountered so far, but I have got to one point that I can't seem to solve.

I have two mysql tables (Categories & Products), I have a page that shows the categories (generated from the categories table) and when a category is selected it lists the products in that category.

Due to stock etc. not all categories always have products associated to them.

The Category & Product Tables are automatically updated, so what I need to do is only show categories that have products associated to them, both tables have a column category_no, and nothing I've tried so far seems to work.

The code I have so far is:-

Code: [Select]
Require_once('config.php');

Mysql_connect(db_host, db_user, db_password);

@mysql_select_db(db_database) or die( "unable to select database");

$query="select * from categories";

$result=mysql_query($query);

$num=mysql_numrows($result);

Mysql_close();
Thanks

Pete

Hey,

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

Code: [Select]
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>
But I also need the category name - So I have to cross reference the following...

wordpdem_wpsc_product_list

id      name
433   itemone
432   itemtwo
431   itemthree

wordpdem_wpsc_item_category_assoc

id   product_id    category_id
1    433                    1
2    432                    2
3    410                    3

wordpdem_wpsc_product_categories

id  name
1   Bread
2   Fish
3   Snacks

I've now written:
Code: [Select]
SELECT
meta.product_id AS pid,
list.name AS name,
list.price AS price,
retailer.category_id AS catid,
category.name AS catname

FROM wordpdem_wpsc_productmeta AS meta
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id )
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id )
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC
However it fails on line 12 (the Where statement...)

Any ideas what I've done wrong?

 
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.

How do I search through table 1, 2, 3 where a=x and b=y and c=z?

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

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....

Hi, I want to be able to retrieve records from db tables to build an xml file. The problem I am having is that I don't know how to retrieve a value after using foreign key to get appropriate value. You see below I want to make a new attribute to store to var $newAttr.

Code: [Select]
<?php
$doc=new DOMDocument();
mysql_connect("localhost","root");
mysql_select_db("xmlDB");

$edges=mysql_query("SELECT * FROM edge");
while($row=mysql_fetch_assoc($edges)) {
if($row['flag']=='val') {
$curValue=mysql_query("SELECT val FROM value,edge WHERE val.edge_id=edge.edge_id");
if($curRowIsAttribute=mysql_query("SELECT is_Attribute FROM value,edge WHERE value.is_Attribute='Y' AND value.edge_id=edge.edge_id")) {
//create attribute node
$newAttr=$dom->createAttribute();//HELP HERE PLEASE THEN I SHOULD BE ABLE TO COMPLETE NEXT TWO LINES BELOW!

//set attribute value
...

//append attribute to current element
...

}//END IF
}//END IF
}//END BIG WHILE 
?>

Any help much appreciated!

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?