PHP :: How To Corret This Mysql Select Query?

Mysql Query - Select Any
I have the following sql statemant to search a mysql database that gets
if values from a form with combo box's in.

SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND
hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND
hottubs.seatsto <= '%s' AND hottubs.shape = '%s' ORDER BY
$thesearchtype_search.

Everything works fine except I want to add a select "any" from the
shape combo box. I really need a way of cutting out the last " AND
hottubs.shape = '%s' " if the $_GET['shape'] = 'any'
Will this work using a variable as shown below??

IF ($_GET['shape'] != 'any' )
{
$shape = AND hottubs.shape = '%s'
}

SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND
hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND
hottubs.seatsto <= '%s' $shape ORDER BY $thesearchtype_search

I have only been doing php about a month so go gentle!

Very Slow MySQL SELECT Query After 11 Records
I have a very weird problem

I moved my database to new IIS 6.0 + PHP 4.3 + MySQL 4.0 setup. I tried to check with myphpadmin if everything works fine, but I couldn't browse my table (125 records). I used SELECT option and found out that SELECT query up to 11 records is ok, but after that, even if I choose to limit query to 12 records, it just hangs.
I tried selecting from the beginning of the table and from the middle - same thing, but when I run query localy on mysql console, everything works fine

MySQL Query - How To Select Names With Out Duplicates
I want to select names from a database
ie:
Dog
Dog
Dog
Cat
Bird
Bird

I want something where I can select >Dog, Cat, Bird instead of the three dogs and two birds. Can I do that?

Drop Down Menu Where The Items Are From A MySQL Select Query
I am just starting programming in PHP and wonder if it is possible to have a
drop down menu where the items are from a MySQL select query.

I am registering people for a weekend seminar and want to have a list of
valid weekends to choose from on the form.

Any pointers to scripts or PHP references that will help would be
appreciated.

I am using the book "Build your Own Database Driven Website Using PHP &
MySQL" to get me started, but also need recommendations for a general primer
to php.

Multiple Select From Listbox And Execute A Sql Query Select Statement
Supposing i have a html form with 2 listbox and i name it status and resolution.

So it goes <select name='status[]'>... <select name='resolution[]'>

Then i have a another php page.
$status = $_POST['status'];
$resolution = $_POST['resolution'];

Now my question is, how do i do the sql query statement?

mysql_query("SELECT * from bugs where status=$status and resolution=$resolution");

The problem is, i select 3 options form the status listbox eg. new, closed, duplicate. And from the resolution, i chose fixed, invalid.

SELECT Error: No Database Selectedin Query: SELECT * FROM Schedule
I got the above error message after reading FAQ #10 which applied to my first error. The thing is, I thought I had selected a database. This script is copied from a tutorial, and I'm just too new at this to spot my error. PHP Code:

Select Query Between Dates
I would like to add a date restriction to this code PHP Code:

Select Query Problem
I use php4 and winxp.

This query works as expected:

$result = mysql_query("
SELECT feventid, UNIX_TIMESTAMP(fdate) as fdate, ftitle, fpostedby,
fdetails, factive, UNIX_TIMESTAMP(fpostdate) as fpostdate
FROM events
WHERE
fdate between '$datefrom' and '$dateto'
and factive
and fpostdate <= curdate()
ORDER BY fdate, fpostdate
");

However, when I add the condition

!(strpos(fdetails,'$fsearchstring')===false),

$result is false.

$result = mysql_query("
SELECT feventid, UNIX_TIMESTAMP(fdate) as fdate, ftitle, fpostedby,
fdetails, factive, UNIX_TIMESTAMP(fpostdate) as fpostdate
FROM events
WHERE
fdate between '$datefrom' and '$dateto'
and factive
and fpostdate <= curdate()
and !(strpos(fdetails,'$fsearchstring')===false)
ORDER BY fdate, fpostdate
");

Even simpler stuff like:

'and strpos('abc','c')>0'

does not work.

I made two more discoveries trying to crack the problem.

Ɔ=0', is an acceptable condition, while 'true' is not.

For the first query, $result does not return true, but 'Resource id #3'.

I have just started with php. What am I doing wrong?

Select Query For Keywords
I am connecting to an Access database. I have the following code that breaks the user submitted title into an array. I want to match only records that have only the array values, but can be in any order. For example:

User enters - "Purple People Eater"

Matching Records - "Purple Eater People", "Eater People Purple",...
but NOT - "The Purple People Eater", "Purple People",...

Here is my code: PHP Code:

Select Query / Loop
My users are organised in a table named 'users'. Each user is a member of a department, but some users are members of more than one department, so I have another table named 'user_department'. I have another table named 'submissions' int which users submissions are stored. Right now I am developing an archive of submissions. Generally speaking a simple query will do the job like this:

$query = "SELECT * FROM submissions WHERE status != 'Pending' AND sid = '$username' ORDER BY id desc LIMIT $from, $max_results";
$result = mysql_query($query);

The problem comes when I try to select only the submissions lodged by the users department. The point being that the department head can view all submissions lodged by his department. If the department head is only head of a single department, this would work:

$query = "SELECT * FROM submissions WHERE status != 'Pending' AND department = '$users_department' ORDER BY id desc LIMIT $from, $max_results";
$result = mysql_query($query);

But if the department head is head of more than one department, I start to get lost! I can loop through the 'user_department' table to find out which departments the user is the head of like this: Code:

Php Query To Select Min Date
im trying to get a minimum date from my database, the current way im doing it is:

SELECT * FROM events where Date > NOW() OR Date = '9999-12-12' ORDER BY Date ASC LIMIT 1

this works in terms of code, but it doesnt do what I want it to do. Its the ORDER BY Date, that is the problem: What I want to select is

The next date OR a date = 9999-12-12

I tried searching for a query like "select MIN date" but couldnt find anything on it, plus the date has to be in the future, hence the need for DATE > NOW()

quite confusing I know!! but any advice?

Multiple SELECT COUNT()'s In One Query?
I'm creating a script that, fairly often throughout, I need to use a SELECT COUNT() query on two seperate tables - this seems to be slowing the script down quite a bit, and if the script remains this slow, it won't be of much use to me...which would be very bad!

Is there anyway to combine these two to grab the COUNT() value from both in one query?

Pass Array Into Select Query ?
I was wondering if it is possible to pass a php array into a mysql query rather than looping through each one eg:

//the array
$namesArray = array("Joe", "Jane", "Bob", "Mary", "Paul", "Eddie", "John");
//the sql
SELECT id FROM users WHERE name='$namesArray'

Arrays, Query Results And Select Box
I want/need to pre-select a drop-down box with a value if it's present in both a lookup table and user account table. After wrestling with this for a few hours I give up.

i know this can't be this complicated, but apparently i'm missing something, so if anyone can suggest a way to go about this i'd be a little more sane. The idea is to have the user value already selected then when they change the value i'll update their account.

Query Select Dates More Than One Hour
i'm searching for the mysql query which can gave me frome DATE_TIME mysql
variable which lines are more than an hour

i can do that with days with
SELECT * FROM clients WHERE TO_DAYS(NOW())-TO_DAYS(date)>7

Query Date Range Select
how do i make a query that select between today and a week from today.

Merging 2 Tables With A Select Query?
I have two tables in my database. One is called 'hotfood', the other is 'coldfood'. What I want to do is have a single SELECT query for both of these tables. This is so I can then use 'mysql_num_rows' on this query, like so.

if(mysql_num_rows($id)==0)
{
print "Food type does not exsist";
}
else
{
--
}

It basicaly to do a search type thing, the above checks that the food name exists by checking both the hot and cold food table, therefore I need to two merged into the select query so that I can search it.

Select Query With WHERE Clause Doesn't Work Any Longer
I have a table in MYSQL that I'm trying to set up a search query on (so it can be searched for records by an author's name). I did it several weeks ago and then it worked, but now when I try to run the search it returns no records although I know they are there. To my knowledge nothing has changed in my PHP script. I checked everyhting repeatedly but must be blind to the error. It's driving my crazy since I know it worked fine at one time.

If anyone has the energy to take a look and offer their suggestions, it would be greatly appreciated. Here is my script. PS. I can add a record and view all the records through other scripts with no problem.

<?php # Script 11.6 - md_seaauthor.php
// This page searches titles by author.

// Set the page title and include the HTML header.
$page_title = 'Search Mother Daughter Bookclub by Author'
include_once ('header4.html');

require_once ('Connections/mysql_connect.php');
// Connect to the database.

$query = "SELECT * FROM mdbookclub WHERE author ='$author'";
$result = mysql_query ($query);
while ($row = mysql_fetch_array($result)) {
$md = $row['md_id'];
$title = stripslashes($row['title']);
$author = stripslashes($row['author']);
$f_author = stripslashes($row['f_author']);
$pub_year = stripslashes($row['pub_year']);
$call_number = ($row['call_number']);
$display_block .= "<p><strong>Author:</strong> $author <strong>Title:</strong> $title<br />
<strong>Publication year:</strong> $pub_year <br />
<strong>Call number:</strong> $call_number </p>";
}
?>

Using Record Identifier From Select In Update Query?
Just wondering if I can get a record identifier from a query. The basic idea is to process the data in a user-specified field in a user-specified table.. and write it back without modifying any other field. So the program would go something like this:

$qid=mysql_query("SELECT $field FROM $table");
WHILE ($list($field_value)=mysql_fetch_row($qid)) {
new_field = process($field_value);

and then the tricky part - how do I write back $field to $table? If I use an update query (eg: UPDATE $table SET $field=$new_field WHERE $field='$field_value') it will update all the fields where $field is $field_value.

Is there a way of getting a (unique) record identifier from the select query and using it in the update query? Since the table is user-specified, I have no way of knowing what the primary index is.

SQL Query With SELECT DISTINCT And LEFT JOIN
I'm having problems with a SELECT DISTINCT statement with an INNER JOIN.

Here is the table structure for my tables:

'locked_accounts'
locked_account_id
uid

'la_comments'
comment_id
locked_case_id
uid
status

'user'
uid
username

And here is my SQL:
SELECT DISTINCT l.locked_case_id as locked_case_id, l.uid, u.uid as uid, u.username as username FROM la_comments l LEFT JOIN users u ON l.uid = u.uid where status = 1

What I'm trying to accomplish is getting a display of updated accounts (where status=1) and I want to display their username. In the la_comments table, there might be 50 distinct 'uid's, but I only want to display the distinct uid/username's where the status = 1.

Can someone point me in the right direction on this. I've messed with this for a couple of hours and I'm at wits end. Not very bright I am when JOIN statements it comes to.

Paginating Random Select Query Results
I am just starting with PHP/MySQL and using Dreamweaver MX 2004.
I am selecting a subset of records from a table, then ordering them using RAND() and then displaying 6 at a time on a page. So far so good but there is one major problem, namely, when you move from page to page viewing the results the query is rerun for each page which means that you end up duplicating results.

What I want to be able to do is run the query once and then page through the returned results without duplication. Also to be able to go back & forth between pages and see the same set of results.

Here's a simplified example: Suppose I have 12 records (call them A - L).
The query : SELECT * FROM Table WHERE Criteria = 1 ORDER BY RAND()
Which returns: D F K A L F J H G C B I
So my pages should show the following:
Page 1: D F K A L F
Page 2: J H G C B I

And I should be able to switch back and forth between these pages and always see exactly the same records each time.
However what I see at the moment is:

Page 1: D F K A L F
Page 2: F B C A E D (Note duplicates from Page 1 because query has been re-run)
Page 1: C B F A K L (Note duplicates again plus results not the same as previous visit to Page 1)

Error In Query: SELECT Uid, Uperms From Users WHERE Uname =
I am trying to set up the article from the main page in devshed called money is time
the thing is this, it denies access for this user and pass even though they are in the database 'example' and on table (users)

Error in query: SELECT uid, uperms from users WHERE uname = 'john' AND upass = PASSWORD('john'). Access denied for user: '@localhost' to database 'example' Code:

Select Count Query, 2 Tables With Identical Structures
I have 2 Tables that have the exact same structures, One is for Inventory, the other is for Relief Inventory (ie. when an item is sold it is moved to relief).

What i want to be able to do is to run a query that will count the number of items tested by one person and display that sorted and grouped by the date cleared. I can get this to work when working with just the Inventory table, but the problem is that as items are moved out to relief the counts will change.

$query = "SELECT COUNT( * ) AS `Count` , `Date Cleared` , `Tested By` FROM `Inventory` WHERE `Tested By` = ".'"'.$tester.'"'." GROUP BY `Date Cleared` ORDER BY `Date Cleared` DESC ";

My question is, how do i run a similar query that will search both tables and combine the results. I have tried adding the other database to the FROM statement, and have been tring to figure out if i can use a join statement but that hasn't yielded any favorable results.

PHP Update Query Mysql: Query Succussful?
I can't seem to successfully test an update query. Below is a piece of code that updates a statistics table. If row today doesn;t exist, a new day must be created. But this doesn't work...

thank you for your reply.

$today= date("Y-m-d");

$qq="update totals_r set hits_r=(hits_r+1), lang_$lang=(lang_$lang+1), cat_$cat=(cat_$cat+1), rating_$rating=(rating_$rating+1), bussite_$bussite=(bussite_$bussite+1) where date='$today'";

$result_6 = mysql_query ("$qq");

if (!$result_6){ /// IF !ROW_TODAY ==> CREATE NEW DAY

$qq_2="insert into totals_r (hits_r, lang_$lang, cat_$cat, rating_$rating, bussite_$bussite, date) values(Ƈ',Ƈ',Ƈ',Ƈ',Ƈ','$today')";

$result_6_2 = mysql_query ("$qq_2");

if (!$result_6_2){
$err_msg_sql_6= mysql_error();
$err_loc_6="$PHP_SELF"." // Query6: update totals_r";
}
}

Why Does The Word "Array" Show On A Select Query
I am new to php and MySQL and I'm attempting to run a select query on a
MySQL database which is working fine except prior to displaying the table
with the results from the select query it displays 1 line for each row in
the database containing only the word Array. This is then followed by the
correct results in the constructed table. I am not storing the string
"Array" in my table.

Following is the PHP code for constructing the table from the results
variable.

for ($i = 0; $i < mysql_num_rows($result); $i++)
{
echo "<tr>";
echo $row = mysql_fetch_row($result);
for ($j = 0; $j < mysql_num_fields($result); $j++)
{
echo("<td>" . $row[$j] . "</td>");
}
echo "</tr>";
}

I hope this is enough of the PHP script to solve the problem

PHP Saying Error In Mysql Syntax, But Written My Mysql Query Browser!
I have a basic db that I access with MySQL query browser. Everything
seems fine to me but I am using this db as part of a php shopping
basket and when I try to add an item I get:

Notice: Query failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '>function.extract]: First argument should be an
array in functions.inc.php on line 31
Notice: Undefined variable: price in functions.inc.php on line 36
Notice: Undefined variable: price in functions.inc.php on line 39
Notice: Undefined variable: total in unctions.inc.php on line 39

I'm assuming the last three are caused by this problem as price should
be passed to the cart, and total is worked out using it. However
although I know mySQL code it was the MySQL query browser that
actually generated the code and I cannot see a way to view or debug
the code.

The db has one table in it which is made up of id, name, subname,
desc, and price.

The code in the php file that is being referred to is:

MySQL Select
Just a qucik question. I need to display everything from the table where the data is not null, apart from username, as they'll always have a username. So far I just have this, which is on the assumption they have entered their name.

$get = "SELECT * from profiles WHERE name IS NOT NULL LIMIT 30";

Is there a way I can basically make it say. Select * from profiles where * but username is NOT NULL?

Php/mysql Select
I need to retrieve the most recent additions to a table where column name='$string'
My question is that of these records returned, I need to diplay THE MOST RECENT record in one place on the html page, and then display the others (in order from newest to oldest) in another place...

I can pull the records with a select statment, and I understand the ORDER BY statement that follows, but how do I split the results as above?

** The records have a 'date/time' column, and a 'category' column that I will use to perform the select statement...**

MySQL SELECT
I have a page where I am trying to list data from a select statement. However, I am only getting one record from this SQL statement in a PHP script, but over 3200 records with the same statement in phpMyAdmin. Am I doing something wrong? PHP Code:

MySQL+PHP Select One Row
I'm sure it has been asked a thousand times before, but still:

How can I access a particular row in my MySQL table via PHP?
The background is just to update the values of one row in the table via
a html form with the old values already filled in.
What I tried so far ist sth like this:

$row=mysql_query('SELECT * FROM names WHERE ID=$entry');
for ($i = 0; $i < mysql_num_fields($row); $i++)
{
echo("<td>$row[$i]</td>
");
}

The mistake should be that mysql_query does not return a vector, but
still a table...

Does anybody have a good idea how to solve that problem?

MYSQL SELECT?
In my code I am trying to SELECT username and UsrRights When I Echo out the SQL query I do not get anything back. Why am I not getting anything echoed back? Code:

Mysql Select Via Php
Everything works. I just want to make a table of results show up in alphabetical order.

I have mysql select whole table. I want to take the mysql select and select the whole table with one field alphabetically. A to Z. How do I do this?

Select From A Mysql Table
I want to pull one field from a mysql table. How do I do that? Do I use SELECT? The field name is $sip_visitornum. And I want the last value that was inputted in to the db. I already have the code to get the visitor number but I don't know how to display data from the database properly. Thanks.

MYSQL - Select XX Months
I have a database set up, with one of the fields is a month -

"200611" = November 2006
"200610" = October 2006
"200609" = September 2006

I was wondering, I know how to sort descending etc.. - Is there a way,
to (add to an existing search criteria).. select all records in the
last XX months..

IE, If I only wanted the last 2 months, it will only select records in
October & November 2006....

or September also - if I want 3 months...

(I have records going back to April 2006)

Im guessing the 'unique' function will be used, but im not sure how to
select XX months.....

Negative SELECT In Mysql?
How does one retrieve the rows in a select statement that DONT match the
select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor
catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN'T in the big distributor catalog?

MySQL SELECT Returns.
How would one go about retrieving data from a MySQL table, and then breaking it up into groups of, for example, 5.

What I am trying to do is pull search results from sed table, and then be able to have links at the bottom that would be like "<Previous 1, 2, 3, 4, 5, 6, Next>" where the numbers represent the dynamic amount of pages listing the search results.

(mysql) Select And Indices
Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem
to want to let me access Index values...

Select Multiple & Mysql
What's the sql should look like to get all possible data when using multiple select,
such as: <select name="hostfield[]" multiple> PHP Code:

Conditional Select From Mysql
I have a mysql database which stores race data that I want to open up to users for viewing. I want the user to be able to select various subsets of the data according to the parameters: race, gender, age, and type (and maybe sometime, by name as well). This is BOTH a mysql question and a php one, as I ask for a more efficient solution (see bottom this post).

Here is a snippet of how I am implementing the mysql statements to select only the exact subset of data. PHP Code:

MySQL Select Case
I'm trying to get the venue in the 'List' column when in contains a
value. But if the venue Is Null then place the event in the 'List'

SELECT
CASE
WHEN event Is Null THEN venue
Else
event
End as 'List'
,DATE_FORMAT(fld_date, '%M') as Monthly[color=blue]
>From na_statistics[/color]

MySQL: Select Row Numbers
I would like to be able to do something like this...

select name,dob from employees where [rownum] > 10 and [rownum] < 20;

.... results from this would show me data from the 10th, 11th,... and 19th
row.

MySQL Select From (no Column)
Is it possible to select from a table using WHERE without putting in a column name? Kind of like this: mysql_query("select * from flights WHERE LIKE '{$searchfield}'");

MYSQL Select Code.
Im trying to make this somewhat statement which says through mysql_query(); somthing like:

("SELECT username = $user FROM user_info WHERE rights = $rights");
// then put $rights which has been never been valued before
$_SESSION['RIGHTS'] = $rights;

So correct me if im wrong i want to do something like this. if anyone could help me form this up in a formal mysql_query manner, since ive done a lil search but didnt find much about it.

Using PHP And MySQL To Populate (select) Menus
I am a newbie to php *gasp* and I am trying to make a <select> drop down menu, but I want the menus populated by information from a field in my database. There are a bunch of entries in the field MUD, that are identical......I would like the menu NOT to repeat identical names. EX... Each name only once, even though it is in the database many times.

MySQL: Insert...select - Update?
I was reading about insert select
http://dev.mysql.com/doc/mysql/en/insert-select.html

Thought this may be a better way to perform a query I'm doing. The differenc is that I need td select from one field, and then update another field in the same record (it is a backup/undo field).

Is there a way to do this in one query? It's a large chunk of data (longtext) so I thought this would be faster than the alternative...

Select List/Menu Using PHP And MYSQL
Here is my code:

<select name="country" class="sidelinks">
<?php
do
{
?>
<option value="<?php echo $row_AllCountries['country_ID']." ";?>"<?php if ($row_AllCountries['country_ID'] = $acountry) { echo "selected"; }?> >
<?php echo $row_AllCountries['countryname'];?></option>
<?php
}
while ($row_AllCountries = mysql_fetch_assoc($AllCountries));
?>
</select>

A few words to describe:
-$row_AllCountries['country_ID'](get from MySQL) is a unique country ID number
-$row_AllCountries['countryname'](get from MySQL) is the name of the country
-$acountry is the country ID number that i want to be selected when the form loads

I have all the values and country names named OK...the only problem is that when the form loads it selects the last country.

MySql Select Unique Rows?
I am have a table which contains recent search querys of another table.

I am tryng to output this data into a list so the user can click, and re-run a recent search...

The problem is that the table is full of duplicate searches from the same user.

How do I select the searches without retrieving duplicates?

SELECT id, term FROM project_term WHERE pid='$_SESSION[project_id]'

Also, would it make more sense to store all serches of a paricular user, or stop the insert if the record exists? Is there a way I can do this without performing 2 queries (ie adding some sort of LIMIT or other xeyword to the INSERT query?) I can't make the column unique because two users could perform the same search...

Mysql SELECT With (varchar) Variable
I have been sent from the mysql forum to here, apparently my problem is strictly PHP related? This one is easy I expect - I need to make a variable that is readable as varchar by mysql - I just think it will be faster to ask here than search all the PHP/MYSQL functions to get it right.

In my SELECT statement I would like to pass names (varchar in my database) via a variable called $membername e.g.: PHP Code: