PHP - Mysql Query Order By Multiple Columns

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=312690.0

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

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=313325.0

Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run.
(Table)
qc_reports

(Fields)
id
report_date
report_lot_number    
report_po
report_supplier    
report_buyer    
report_inspectedby    
report_pulptemprange    
report_carrierconditions
report_supplierclaim    
report_carrierclaim    
report_temprecorder    
report_temprange_N    
report_temprange_M    
report_temprange_B    
report_suppliercontact
report_contactedby    
report_time    
report_comments

(Table)
qc_lots

(Fields)
id
report_id
lot_temprange    
lot_commodity    
lot_rpcs    
lot_brand    
lot_terms    
lot_cases    
lot_orgn    
lot_estnum
lot_avgnum

This is the query that I'm trying to do.

Code: [Select]
<?php
$sql = "SELECT * FROM qc_reports, qc_lots WHERE  ";
if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND ";
if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND ";
if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND ";
if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND ";
if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND ";
if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND ";
 if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND ";
if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND ";

$sql = substr($sql, 0, -4);

$query = mysql_query($sql);


$numrows = mysql_num_rows($query);
?>
RESULTS - <?php echo $numrows; ?>
<hr>
<table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td>&nbsp;</td></tr>
<tr>
<td>&nbsp;</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($query))
{
$id = stripslashes($row['id']);
$report_lot_number = stripslashes($row['report_lot_number']);
$report_po = stripslashes($row['report_po']);
$report_date = stripslashes($row['report_date']);

echo "<tr>
<td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td>
</tr>";
}
echo '</table><br><br><br><hr><br><br>';

}

?>
All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table.

I'm stuck and need some guidance. Can someone help please?

I want to have a search product feature, but I would like members to be able to search multiple fields in one go i.e.  product_code, Product_name in one MySQL query.

The thing is, members have to be logged on, so the query must also only show results relating to that specific member, via the session[member_ID], my current query for listing products for that specific member is :

Code: [Select]
$sql = "SELECT productId, productCode, image, name, price, stock_level FROM product_inventory WHERE memberr_ID = '" . $_SESSION['SESS_mem_ID'] . ";
How would I change the above into a search query to search for productcode, productname and still only show results beloging to this member using the session data ? 

all help appreciated

I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is :
Code: [Select]
USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona -->
----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron -->
-------Adamsville [2]
-------Addison[5]
-------Akron[1]
......(list of other City)
----Alaska [ 3 ]
-------Avondale[3]
......(list of other City)
----Arizona [ 2 ]
-------College[2]
......(list of other City)
Lisintg Table :

Code: [Select]
ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE
 1 -- TEST -- 1#2#3    -- 20110101 -- 1
 2 -- TEST1 -- 1#2#3    -- 20110101 -- 1
 3 -- TEST2 -- 1#3#5    -- 20110101 -- 1
 4 -- TEST3 -- 1#7#6    -- 20110101 -- 1
Country Table :

Code: [Select]
id       -- name 
 1        -- USA 
stats Table :

Code: [Select]
id        -- countryid -- name 
 1        -- 1             -- albama
 2        -- 1             -- alaska
 3        -- 1             -- akron
town Table :
Code: [Select]
id        -- countryid -- statsid -- name 
 1        -- 1             -- 1         -- adamsville
 2        -- 1             -- 1         -- addison
 3        -- 1             -- 1         -- akron
Thanks For Any Help.

Given the below 3 database tables I am trying to construct a SQL query that will give me the following result:

customer_favourites.cust_id
customer_favourites.prod_id OR product.id
product.code
product.product_name
product.hidden
product_ section.section_id (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.catpage (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.relative_order (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)

I currently have....

SELECT customer_favourites.cust_id, customer_favourites.prod_id, product.code, product.product_name, product.hidden, product_section.section_id, product_section.relative_order, product_section.catpage  
FROM customer _favourites  
INNER JOIN product ON customer_favourites.prod_id = product.id  
INNER JOIN product_section ON product_section.product_code = product.code  
WHERE `cust_id` = '17' AND `hidden` = '0' GROUP BY `code`  
ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC LIMIT 0,30

This gives me what I want but only sometimes, at other times it randomly selects any row from the product_section table.

I was hoping that by having the row I want as the last row (most recent added) in the product_section table then it would select that row by default but it is not consistent.

Somehow, I need to be able to specify which row to return in the product_section table, it needs to be the row with the lowest section_id value or it should by the last row (most recent).

Pulling my hair out so any help is gratefully received.  
customer_favourites

id     cust_id   prod_id
70    4            469
product

id        code           product_name    hidden
469    ABC123     My Product        0
product_section

id                  section_id      catpage    product_code    relative_order     recommended
44105         19                   232            ABC123             260                    1
44106          3                     125            ABC123              87                      1
44107          2                     98              ABC123             128                     1
44108          1                    156             ABC123             58                        0

 

Hello all,

   I am trying to learn OOP in PHP so please forgive my ignorance. I seem to be having difficulty inserting data into my database from checkbox items. My database has a column for each each fruit. I would like for each column to be populated with either a one(1) for checked or a zero(0)  for not checked. Currently, my attempts have failed. I can either get all checkboxes to insert all zeros or I can get the first column to insert a one(1) while the others as zeros. Is there anybody here that can help me figure out what I am doing wrong and help me to re-work my code so I can get this to work? If anybody can help me using OOP methods that would be fantastic. Thank you all in advance.

 

$preffruit->create_preffruit(array(

  'fruit_apple' => escape(Input::get(['fruit_selection'][0]) ? 1 : 0),
  'fruit_orange' => escape(Input::get(['fruit_selection'][1]) ? 1 : 0),
  'fruit_banana' => escape(Input::get(['fruit_selection'][2]) ? 1 : 0),
  'fruit_pear' => escape(Input::get(['fruit_selection'][3]) ? 1 : 0),
  'fruit_kiwi' => escape(Input::get(['fruit_selection'][4]) ? 1 : 0)

  ));

<input type="checkbox" name="fruit_selection[]" value="fruit_apple">
<input type="checkbox" name="fruit_selection[]" value="fruit_orange">
<input type="checkbox" name="fruit_selection[]" value="fruit_banana">
<input type="checkbox" name="fruit_selection[]" value="fruit_pear">
<input type="checkbox" name="fruit_selection[]" value="fruit_kiwi">



public function insert_preffruit($table, $fields = array()) {
			
			$keys = array_keys($fields);
			$values = '';
			$x = 1;			
			
			foreach($fields as $field) {
				$values .= '?';
				if($x < count($fields)) {
					$values .= ', ';
				}
				$x++;
			}		
				
				$sql = "INSERT INTO preffruit (`user_id`, `" . implode('`, `', $keys) . "`) VALUES (LAST_INSERT_ID(), {$values})";		
			
			if(!$this->query($sql, $fields)->error()) {
				return true;
			}			
			
			return false;
		}

 

Edited September 23, 2020 by ke-jo

I want to echo out a few subcategories per main category.
It should be a maximum of 20 entries per column

At this stage I have the following... It echo's the results out in 4 columns and not what I want.
Any suggestions?

$query = "SELECT 
adsubcat.name AS subname, adsubcat.linkname AS sublink, adcat.clinkname AS clink
FROM adsubcat 
JOIN adcat ON adcat.id=adsubcat.catid
WHERE adsubcat.catid='$catid' ORDER BY adsubcat.name ASC";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

$count = 0;

print '<table style="text-align:left;margin-left:0px;width:943px;">'."\n";
while ($row = mysql_fetch_assoc($result)){
$gal = '<a href="'.$root.'/'.$row['clink'].'/'.$row['sublink'].'">'.$row['subname'].'</a>';
if ($count == 0){
print '<tr>';
}
++$count;
print '<td>'.$gal.'</td>';
if ($count == 4){
$count = 0;
print'</tr>'."\n";
}
}
print'</table>'."\n";
}

Hello all! I'm having some issues with a snippet that I found online and have edited to how I want it.

Basically the code works at the moment, except if I were to search for 2 or 3 terms, it would search the columns username/action/result/changes for any result that have any of the words rather than results that include both terms, instead of either.

I want the query to be like (assuming 2 search terms):

Quote

username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%' AND username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%'


and not:

Quote

username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%' OR username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%'


It needs an AND instead of an OR, but I'm not sure how to go around doing it.

Here's the code as it is at the moment:

$filter = ($_POST['filter']); 

$query = "SELECT * FROM activity WHERE"; 

$searchresult = explode(" ", $filter); 

     foreach ($searchresult as $key => $filter) { 
$query .= " username LIKE '%$filter%' OR action LIKE '%$filter%' OR result LIKE '%$filter%' OR changes LIKE '%$filter%'"; 
     if ($key != (sizeof($searchresult) - 1))   
$query .= " AND ";
     }
$query .= "ORDER BY id DESC"; 

Sorry if this is a little confusing to understand, hope someone understands what I need.

Script:

<?php

    $tqs_admin_flag = "SELECT * FROM `flags`";
    $tqr_admin_flag = mysqli_query($dbc, $tqs_admin_flag) or die(mysqli_error($dbc));

while($row_admin_flag = mysqli_fetch_array($tqr_admin_flag)){

        //print_r($row_admin_flag);

        $tqs_admin_flag_thread = "SELECT * FROM `thread` WHERE `id` = '" . $row_admin_flag['thread_id'] . "' ORDER BY `date_created` DESC";
        $tqr_admin_flag_thread = mysqli_query($dbc, $tqs_admin_flag_thread) or die(mysqli_error($dbc));

    while($row = mysqli_fetch_assoc($tqr_admin_flag_thread)){

        include($_SERVER['DOCUMENT_ROOT'] . "/gallerysite/admin_flag_thread.php");


    }
}

?>

This in comparison works:

$tqs_admin_flag_thread = "SELECT * FROM `thread` ORDER BY `date_created` DESC";

I would appreciate suggestions for which reasons the above script (the way it is) may not work.   With the above script the querying and printing on screen does happen, though the "ordering by DESC" does not happen.
Edited by glassfish, 24 October 2014 - 11:38 AM.

I am attempting to order the results of a query using the get value but they wont order.

here is the form:

echo "<form name=\"order_form\" action=\"{$site_root}/index.php\" method=\"GET\">";
echo "<input type=\"hidden\" value=\"{$forum_id}\" name=\"forum\" />";

echo "<select name=\"order_by\" style=\"margin-left:5px;\">";
echo "<option value=\"asc\" name=\"order_option\" />Order By: Ascending</option>";
echo "<option value=\"desc\" name=\"order_option\" />Order By: Descending</option>";
echo "</select>";

echo "<select name=\"sort_by\" style=\"margin-left:5px;\">";
echo "<option value=\"topic_name\" name=\"sort_option\" />Sort By: Topic Name</option>";
echo "<option value=\"topic_poster\" name=\"sort_option\" />Sort By: Topic Author</option>";
echo "<option value=\"topic_time_posted\" name=\"sort_option\" />Sort By: Time Posted</option>";
echo "<option value=\"topic_views\" name=\"sort_option\" />Sort By: Topic Views</option>";
echo "<option value=\"topic_replies\" name=\"sort_option\" />Sort By: Topic Replies</option>";
echo "<option value=\"topic_last_poster\" name=\"sort_option\" />Sort By: Last Poster</option>";
echo "<option value=\"topic_last_post_time\" name=\"sort_option\" />Sort By: Last Post Time</option>";
echo "</select>";

echo "<input type=\"submit\" value=\"Order\" />";
echo "</form>";

here is the query:

$topic_info_query = $db->query("SELECT 
f.forum_id, f.forum_name,
m.user_id, m.user_username, m.user_group,
t.thread_topic_id, t.topic_name, t.topic_poster, t.topic_time_posted, t.topic_views,
t.topic_replies, t.topic_last_poster, t.topic_last_post_time, t.topic_locked,
t.topic_sticky, t.topic_edited, t.topic_last_poster_id, t.topic_last_poster_group,
t.topic_icon
FROM ".DB_PREFIX."topics as t

LEFT JOIN ".DB_PREFIX."members as m
ON t.topic_poster = m.user_username

LEFT JOIN ".DB_PREFIX."forums as f
ON t.forum_id = f.forum_id

WHERE t.forum_id = '$forum_id'
'".$sort_by . ' ' . $order_by."'")

and here is where $sort_by and $order_by are defined:

if (isset($_GET['order_by']))
{
$order_by = mysql_real_escape_string($_GET['order_by']);
$order_by = strtoupper($order_by);
}

if (isset($_GET['sort_by']))
{
$sort_by = 'ORDER BY t.'.mysql_real_escape_string($_GET['sort_by']);
}

when i echo '".$sort_by . ' ' . $order_by."' which is how it appears in the query i get:

t.topic_last_post_time DESC

which is exactly right. But the results are not being sorted. The echoed variables change as does the url but no sorting happens.

any ideas?

I have a basic query where I am retrieving project records. I basically want to show the records from newest to oldest, but I also want to show the Featured projects first. Each Featured project is marked by "1" in the mysql table column.  If it's not featured, then it's marked "0".

$find_records = $db->prepare("SELECT * FROM projects ORDER BY project_id DESC, featured DESC LIMIT 10");
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    
  }
}

The issue with above query is that the ORDER BY works only for the first component(project_id) and ignores the second component(featured). Do you see what's wrong with it?

i have this query and i'm trying to order all of the results by `created` ascending. I've tried putting the later query before the first but the single row from the first query(the way it is right now) gets stuck as the last row in the results. How can i order the results of both queries?

Code: [Select]
(
SELECT *
FROM Account_activity
WHERE DATE( created ) <  '2011-08-01'
AND username =  '40'
ORDER BY created DESC
LIMIT 1
)
UNION (
SELECT *
FROM Account_activity
WHERE MONTH( created ) =  '08'
AND YEAR( created ) =  '2011'
AND username =  '40'
ORDER BY created ASC
)

I am trying to use the following query with sorting:
$query = mysql_query("SELECT * FROM Table WHERE Date LIKE '$PeriodSelected' ORDER BY Date DESC"); 
$PeriodSelected is basically supposed to get me all results from month selected and it works fine. Now I am trying to add sorting to this query and although I get no errors it does not matter if I use ASC or DESC my results are sorted in the same way. Not sure what I am doing wrong.