Re: [PHP-DB] Another newbie question
charset=iso-8859-15 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Am Montag, 6. September 2004 23:50 schrieb Pete Holsberg: [...] $sql =3D SELECT * FROM $table ; $sql .=3D WHERE `LastName` LIKE $search_string ; $sql .=3D OR `FirstName` LIKE $search_string ; $sql .=3D OR `Spouse` LIKE $search_string ; $sql .=3D OR `Street` LIKE $search_string ; $sql .=3D OR `Email` LIKE $search_string LIMIT 0, 30 ; $sql .=3D ORDER BY Street, HouseNum, LastName; How about =20 $search_string .=3D $search_string . %; $sql =3D SELECT * FROM $table ; $sql .=3D WHERE LastName LIKE '$search_string' ; $sql .=3D OR FirstName LIKE '$search_string' ; $sql .=3D OR Spouse LIKE '$search_string' ; $sql .=3D OR Street LIKE '$search_string' ; $sql .=3D OR Email LIKE '$search_string' LIMIT 0, 30 ; $sql .=3D ORDER BY Street, HouseNum, LastName; $result =3D mysql_query($sql) or die (Couldn't execute query.); ... - Why is that? Also, how do I make LIKE insensitive to case? As far as I know LIKE is case insensitve. And finally, how do I use the % wildcard with a variable? See above. You're welcome =2D-=20 Wolfgang Ebneter MSc. Data Engineering =46ingerprint : 9C66 28A8 7AD8 BA59 6CBB 055C D664 D8FA 8CBA 800C Key-Server : wwwkeys.eu.pgp.net pgpTWvqAHKVAu.pgp Description: signature
Re: [PHP-DB] Another newbie question
On Mon, 6 Sep 2004 [EMAIL PROTECTED] wrote: you should copy/paste that into a command-line msyql session and see if that works. I cut the query back to SELECT * FROM Directory WHERE `LastName` LIKE Joe ORDER BY Street, HouseNum, LastName and ran it manually. I got --- SQL-query : SELECT * FROM Directory WHERE LastName LIKE Joe ORDER BY Street, HouseNum, LastName LIMIT 0 , 30 MySQL said: Unknown column 'Joe' in 'where clause' Perhaps I don't understand LIKE. LastName is a column; Joe is a value. ??? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Another newbie question
I think you need to put '' around joe 'joe' Stuart --- Pete Holsberg [EMAIL PROTECTED] wrote: On Mon, 6 Sep 2004 [EMAIL PROTECTED] wrote: you should copy/paste that into a command-line msyql session and see if that works. I cut the query back to SELECT * FROM Directory WHERE `LastName` LIKE Joe ORDER BY Street, HouseNum, LastName and ran it manually. I got --- SQL-query : SELECT * FROM Directory WHERE LastName LIKE Joe ORDER BY Street, HouseNum, LastName LIMIT 0 , 30 MySQL said: Unknown column 'Joe' in 'where clause' Perhaps I don't understand LIKE. LastName is a column; Joe is a value. ??? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Another newbie question
--- Pete Holsberg [EMAIL PROTECTED] wrote: On Mon, 6 Sep 2004 [EMAIL PROTECTED] wrote: you should copy/paste that into a command-line msyql session and see if that works. I cut the query back to SELECT * FROM Directory WHERE `LastName` LIKE Joe ORDER BY Street, HouseNum, LastName and ran it manually. I got --- SQL-query : SELECT * FROM Directory WHERE LastName LIKE Joe ORDER BY Street, HouseNum, LastName LIMIT 0 , 30 MySQL said: Unknown column 'Joe' in 'where clause' Perhaps I don't understand LIKE. LastName is a column; Joe is a value. ??? On Mon, 6 Sep 2004, Stuart Felenstein wrote: I think you need to put '' around joe 'joe' Stuart Tried that. It said that 'Joe' was an unknown column! Besides, Joe is the value of a variable passed by another script. Thanks. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Another newbie question
OK, Matt, so you've got the basics =8).
Your beginning is good, using the $Offset. Now you only have to
add navigation-buttons (First, Previos, Next, Last) with html
and a hidden field namded Offset.
LIMIT is defined as a constant. You could also use your $Limit.
here is a snippet of my navigation code:
//
// Event-Handler
if ( isset( $Offset ) )
{
// First-Button
if ( isset( $First ) and !empty( $First ) )
{
$Offset = 0;
}
// Next-Button
if ( isset( $Next ) and !empty( $Next ) )
{
if ( $Offset = ( $num_rows - LIMIT ) )
{
$Offset += LIMIT;
}
}
// Previous-Button
if ( isset( $Prev ) and !empty( $Prev ) )
{
if ( $Offset = LIMIT )
{
$Offset -= LIMIT;
}
else
{
$Offset = 0;
}
}
// Last-Button
if ( isset( $Last ) and !empty( $Last ) )
{
$Offset = ($num_rows - LIMIT);
}
}
else
{
$Offset = 0;
}
// End Event-Handler
//
Hope this helps. If there are still questions ... ask =8)
Greetinx,
Mike
Michael Rudel
- Web-Development, Systemadministration -
___
Suchtreffer AG
Bleicherstrae 20
D-78467 Konstanz
Germany
fon: +49-(0)7531-89207-17
fax: +49-(0)7531-89207-13
e-mail: mailto:[EMAIL PROTECTED]
internet: http://www.suchtreffer.de
___
-Original Message-
From: Matt Coyne [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 26, 2001 1:39 PM
To: PHP DB
Subject: [PHP-DB] Another newbie question
Hi guys/gals
Having had my head sorted out by this list already today, I
am in need of
some more help...!!
I have an input form that, through php queries a MySQL db and
passes results
back and the browser displays them (at this stage just as a list).
I have been looking into the LIMIT command to split my
results into sections
(ie results 1-10 then 11-20 etc). No problem in understanding
how LIMIT
works and I have LIMITed the first set to the first 10
results. I understand
that to show results 11-20 I would use LIMIT 10, 10 in my SQL query.
However, how am I to update the LIMIT n, 10 dynamically
depending on what
split of results they are viewing. ie How do I recall the SQL
query but with
an update LIMIT value
Here is what I have been doing:
// ASSEMBLE SQL QUERY
// make the select
$select = "SELECT ref, firstname, surname ";
//add the tablename to the query
$fromtable = "FROM $table_name";
//$fromtable .= "$tablename";
//build where clause
$control=1;
$where = " WHERE $control=1 ";
//determine if field sent value, build statement if true
if ($sex != '') {
$Qsex = "AND sex = '$sex' ";
} else {
$Qsex = "";
}
if ($heightFt != '') {
$QheightFt = "AND heightFt = '$heightFt' ";
} else {
$QheightFt = "";
}
if ($heightIn != '') {
$QheightIn = "AND heightIn LIKE '$heightIn%' ";
} else {
$QheightIn = "";
}
if ($neck != '') {
$Qneck = "AND neck LIKE '$neck%' ";
} else {
$Qneck = "";
}
if ($chest != '') {
$Qchest = "AND chest LIKE '$chest%' ";
} else {
$Qchest = "";
}
if ($waist != '') {
$Qwaist = "AND waist LIKE '$waist%' ";
} else {
$Qwaist = "";
}
if ($hips != '') {
$Qhips = "AND hips LIKE '$hips%' ";
} else {
$Qhips = "";
}
if ($leg != '') {
$Qleg = "AND leg LIKE '$leg%' ";
} else {
$Qleg = "";
}
if ($shoe != '') {
$Qshoe = "AND shoe LIKE '$shoe%' ";
} else {
$Qshoe = "";
}
if ($dress != '') {
$Qdress = "AND dress LIKE '$dress%' ";
} else {
$Qdress = "";
}
if ($hair != '') {
$Qhair = "AND hair LIKE '$hair%' ";
} else {
$Qhair = "";
}
if ($eyes != '') {
$Qeyes = "AND eye LIKE '$eyes%' ";
} else {
$Qeyes = "";
}
if ($skin != '') {
$Qskin = "AND skin LIKE '$skin%' ";
} else {
$Qskin = "";
}
if ($nationality != '') {
$Qnationality = "AND nationality LIKE '$nationality%' ";
} else {
$Qnationality = "";
}
// specify how results are ordered
$orderby = "ORDER BY surname ";
// offset is a hidden value in the initial form == 0
if ($offset = '0') {
$offset = 0;
}
// specify LIMIT
$limit = "LIMIT ";
$limit .= $offset;
$limit .= ", 10";
//build concatenated query
$concatsql = $select;
$concatsql .= $fromtable;
$concatsql .= $where;
$concatsql .= $Qsex;
$concatsql .= $QheightFt;
$concatsql .= $QheightIn;
$concatsql .= $Qneck;
$concatsql .= $Qchest;
$concatsql .= $Qwaist;
$concatsql .= $Qhips;
$concatsql .= $Qleg;
$concatsql .= $Qshoe;
$concatsql .= $Qdress;
$concatsql .= $Qhair;
$concatsql .= $Qeyes;
$concatsql .= $Qskin;
$concatsql .= $Qnationality;
$concatsql .= $orderby;
$concatsql .= $limit;
// make query
Re: [PHP-DB] Another newbie question
Hi Michael
Thanks for the prompt reply, much appreciated
I can see where the code snippet is going. Creating values for $offset for
the set of navigation buttons.
But how do I use those values?
I am stumped on (and am prolly just blinkered) how to make the query again
from the nav button?
If I am barking madly up the wrong tree, then I apologise. I'm not sure if I
have covered the basics, more like I have learnt what I thought I needed as
I went along. There are bound to be large gaping wholes in my knowledge, so
further help is definitely required.
TIA
matt
t h r e e z e r o : : :
the mill, millstone lane, leicester, le1 5jn
e : [EMAIL PROTECTED] :: m : 07747 845690
w : http://www.threezero.co.uk
: : :t h r e e z e r o
From: "Michael Rudel" [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
Date: Mon, 26 Mar 2001 14:48:20 +0200
To: "'Matt Coyne'" [EMAIL PROTECTED], "'PHP DB'" [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Another newbie question
OK, Matt, so you've got the basics =8).
Your beginning is good, using the $Offset. Now you only have to
add navigation-buttons (First, Previos, Next, Last) with html
and a hidden field namded Offset.
LIMIT is defined as a constant. You could also use your $Limit.
here is a snippet of my navigation code:
//
// Event-Handler
if ( isset( $Offset ) )
{
// First-Button
if ( isset( $First ) and !empty( $First ) )
{
$Offset = 0;
}
// Next-Button
if ( isset( $Next ) and !empty( $Next ) )
{
if ( $Offset = ( $num_rows - LIMIT ) )
{
$Offset += LIMIT;
}
}
// Previous-Button
if ( isset( $Prev ) and !empty( $Prev ) )
{
if ( $Offset = LIMIT )
{
$Offset -= LIMIT;
}
else
{
$Offset = 0;
}
}
// Last-Button
if ( isset( $Last ) and !empty( $Last ) )
{
$Offset = ($num_rows - LIMIT);
}
}
else
{
$Offset = 0;
}
// End Event-Handler
//
Hope this helps. If there are still questions ... ask =8)
Greetinx,
Mike
Michael Rudel
- Web-Development, Systemadministration -
___
Suchtreffer AG
Bleicherstrae 20
D-78467 Konstanz
Germany
fon: +49-(0)7531-89207-17
fax: +49-(0)7531-89207-13
e-mail: mailto:[EMAIL PROTECTED]
internet: http://www.suchtreffer.de
___
-Original Message-
From: Matt Coyne [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 26, 2001 1:39 PM
To: PHP DB
Subject: [PHP-DB] Another newbie question
Hi guys/gals
Having had my head sorted out by this list already today, I
am in need of
some more help...!!
I have an input form that, through php queries a MySQL db and
passes results
back and the browser displays them (at this stage just as a list).
I have been looking into the LIMIT command to split my
results into sections
(ie results 1-10 then 11-20 etc). No problem in understanding
how LIMIT
works and I have LIMITed the first set to the first 10
results. I understand
that to show results 11-20 I would use LIMIT 10, 10 in my SQL query.
However, how am I to update the LIMIT n, 10 dynamically
depending on what
split of results they are viewing. ie How do I recall the SQL
query but with
an update LIMIT value
Here is what I have been doing:
// ASSEMBLE SQL QUERY
// make the select
$select = "SELECT ref, firstname, surname ";
//add the tablename to the query
$fromtable = "FROM $table_name";
//$fromtable .= "$tablename";
//build where clause
$control=1;
$where = " WHERE $control=1 ";
//determine if field sent value, build statement if true
if ($sex != '') {
$Qsex = "AND sex = '$sex' ";
} else {
$Qsex = "";
}
if ($heightFt != '') {
$QheightFt = "AND heightFt = '$heightFt' ";
} else {
$QheightFt = "";
}
if ($heightIn != '') {
$QheightIn = "AND heightIn LIKE '$heightIn%' ";
} else {
$QheightIn = "";
}
if ($neck != '') {
$Qneck = "AND neck LIKE '$neck%' ";
} else {
$Qneck = "";
}
if ($chest != '') {
$Qchest = "AND chest LIKE '$chest%' ";
} else {
$Qchest = "";
}
if ($waist != '') {
$Qwaist = "AND waist LIKE '$waist%' ";
} else {
$Qwaist = "";
}
if ($hips != '') {
$Qhips = "AND hips LIKE '$hips%' ";
} else {
$Qhips = "";
}
if ($leg != '') {
$Qleg = "AND leg LIKE '$leg%' ";
} else {
$Qleg = "";
}
if ($shoe != '') {
$Qshoe = "AND shoe LIKE '$shoe%' ";
} e
RE: [PHP-DB] Another newbie question
Ok, so you have to put the 'Offset-Event-Handler' prior to your
select-statement. $Offset is calculatet by the event-handler and
then you specify the LIMIT (already done in your skript):
// specify LIMIT
$limit = "LIMIT ";
$limit .= $Offset;
$limit .= ", 10";
... here comes your query ...
after displaying the results, build something like this (in HTML):
FORM Name="Navigation" Action="?php echo $PHP_SELF; ?" Method="POST"
INPUT Type="Submit" Name="First" Value="First"
INPUT Type="Submit" Name="Prev" Value="Prev."
INPUT Type="Submit" Name="Next" Value="Next"
INPUT Type="Submit" Name="Last" Value="Last"
INPUT Type="Hidden" Name="Offset" Value="?php echo $Offset; ?"
/FORM
... so you have your $Offset (initially zero) stored in the hidden field.
If you hit a Button, then the skript loads itself new and it has now the
var's $Offset which will be new calculatet regarding of the Button you hit.
Simple, ain't ?! 8)=)
I hope, you're getting closer.
BTW, there are ready Navigateable Recordset-Classes for results in
http://phpclasses.upperdesign.com/
Greetinx,
Mike
-Original Message-
From: Matt Coyne [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 26, 2001 3:22 PM
To: PHP DB
Subject: Re: [PHP-DB] Another newbie question
Hi Michael
Thanks for the prompt reply, much appreciated
I can see where the code snippet is going. Creating values
for $offset for
the set of navigation buttons.
But how do I use those values?
I am stumped on (and am prolly just blinkered) how to make
the query again
from the nav button?
If I am barking madly up the wrong tree, then I apologise.
I'm not sure if I
have covered the basics, more like I have learnt what I
thought I needed as
I went along. There are bound to be large gaping wholes in my
knowledge, so
further help is definitely required.
TIA
matt
t h r e e z e r o : : :
the mill, millstone lane, leicester, le1 5jn
e : [EMAIL PROTECTED] :: m : 07747 845690
w : http://www.threezero.co.uk
: : :t h r e e z e r o
From: "Michael Rudel" [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
Date: Mon, 26 Mar 2001 14:48:20 +0200
To: "'Matt Coyne'" [EMAIL PROTECTED], "'PHP DB'"
[EMAIL PROTECTED]
Subject: RE: [PHP-DB] Another newbie question
OK, Matt, so you've got the basics =8).
Your beginning is good, using the $Offset. Now you only have to
add navigation-buttons (First, Previos, Next, Last) with html
and a hidden field namded Offset.
LIMIT is defined as a constant. You could also use your $Limit.
here is a snippet of my navigation code:
//
// Event-Handler
if ( isset( $Offset ) )
{
// First-Button
if ( isset( $First ) and !empty( $First ) )
{
$Offset = 0;
}
// Next-Button
if ( isset( $Next ) and !empty( $Next ) )
{
if ( $Offset = ( $num_rows - LIMIT ) )
{
$Offset += LIMIT;
}
}
// Previous-Button
if ( isset( $Prev ) and !empty( $Prev ) )
{
if ( $Offset = LIMIT )
{
$Offset -= LIMIT;
}
else
{
$Offset = 0;
}
}
// Last-Button
if ( isset( $Last ) and !empty( $Last ) )
{
$Offset = ($num_rows - LIMIT);
}
}
else
{
$Offset = 0;
}
// End Event-Handler
//
Hope this helps. If there are still questions ... ask =8)
Greetinx,
Mike
Michael Rudel
- Web-Development, Systemadministration -
___
Suchtreffer AG
Bleicherstrae 20
D-78467 Konstanz
Germany
fon: +49-(0)7531-89207-17
fax: +49-(0)7531-89207-13
e-mail: mailto:[EMAIL PROTECTED]
internet: http://www.suchtreffer.de
___
-Original Message-
From: Matt Coyne [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 26, 2001 1:39 PM
To: PHP DB
Subject: [PHP-DB] Another newbie question
Hi guys/gals
Having had my head sorted out by this list already today, I
am in need of
some more help...!!
I have an input form that, through php queries a MySQL db and
passes results
back and the browser displays them (at this stage just as a list).
I have been looking into the LIMIT command to split my
results into sections
(ie results 1-10 then 11-20 etc). No problem in understanding
how LIMIT
works and I have LIMITed the first set to the first 10
results
RE: [PHP-DB] Another newbie question
Hello
I too wanted to have next/previous links...
So I found this and it's the best out there...
http://www.oreillynet.com/pub/a/php/2000/11/02/next_previous.html?page=1
to output the query...
echo $data-Print_Name //Or whatever you want to
display
.
Within that code you will be able to use Tables and suchThere is
more to the code...
So download the example and nav.php and you will be able to understand
how he did it.
mysql_fetch_object can be used for many thingslike for a EDIT/VIEW
entry links
printf ("TD WIDTH=80a href=\"%s?Art_Job_Number=%s\"%s %s/a\n",
"list.php", $data-Art_Job_Number, "BEdit/B", "BView/B");
Since I have a lot of Columns the page was to big to display so I choose
to only list 3 columns and then click the link to view all of the Data.
Just like PHPMYADMIN
I hope this helps.Oh navbar.php is a class...
Dan
--
From: Matt Coyne
Sent: Monday, March 26, 2001 5:39 AM
To: PHP DB
Subject: [PHP-DB] Another newbie question
Hi guys/gals
Having had my head sorted out by this list already today, I am in need
of
some more help...!!
I have an input form that, through php queries a MySQL db and passes
results
back and the browser displays them (at this stage just as a list).
I have been looking into the LIMIT command to split my results into
sections
(ie results 1-10 then 11-20 etc). No problem in understanding how
LIMIT
works and I have LIMITed the first set to the first 10 results. I
understand
that to show results 11-20 I would use LIMIT 10, 10 in my SQL query.
However, how am I to update the LIMIT n, 10 dynamically depending on
what
split of results they are viewing. ie How do I recall the SQL query
but with
an update LIMIT value
Here is what I have been doing:
// ASSEMBLE SQL QUERY
// make the select
$select = "SELECT ref, firstname, surname ";
//add the tablename to the query
$fromtable = "FROM $table_name";
//$fromtable .= "$tablename";
//build where clause
$control=1;
$where = " WHERE $control=1 ";
//determine if field sent value, build statement if true
if ($sex != '') {
$Qsex = "AND sex = '$sex' ";
} else {
$Qsex = "";
}
if ($heightFt != '') {
$QheightFt = "AND heightFt = '$heightFt' ";
} else {
$QheightFt = "";
}
if ($heightIn != '') {
$QheightIn = "AND heightIn LIKE '$heightIn%' ";
} else {
$QheightIn = "";
}
if ($neck != '') {
$Qneck = "AND neck LIKE '$neck%' ";
} else {
$Qneck = "";
}
if ($chest != '') {
$Qchest = "AND chest LIKE '$chest%' ";
} else {
$Qchest = "";
}
if ($waist != '') {
$Qwaist = "AND waist LIKE '$waist%' ";
} else {
$Qwaist = "";
}
if ($hips != '') {
$Qhips = "AND hips LIKE '$hips%' ";
} else {
$Qhips = "";
}
if ($leg != '') {
$Qleg = "AND leg LIKE '$leg%' ";
} else {
$Qleg = "";
}
if ($shoe != '') {
$Qshoe = "AND shoe LIKE '$shoe%' ";
} else {
$Qshoe = "";
}
if ($dress != '') {
$Qdress = "AND dress LIKE '$dress%' ";
} else {
$Qdress = "";
}
if ($hair != '') {
$Qhair = "AND hair LIKE '$hair%' ";
} else {
$Qhair = "";
}
if ($eyes != '') {
$Qeyes = "AND eye LIKE '$eyes%' ";
} else {
$Qeyes = "";
}
if ($skin != '') {
$Qskin = "AND skin LIKE '$skin%' ";
} else {
$Qskin = "";
}
if ($nationality != '') {
$Qnationality = "AND nationality LIKE '$nationality%' ";
} else {
$Qnationality = "";
}
// specify how results are ordered
$orderby = "ORDER BY surname ";
// offset is a hidden value in the initial form == 0
if ($offset = '0') {
$offset = 0;
}
// specify LIMIT
$limit = "LIMIT ";
$limit .= $offset;
$limit .= ", 10";
//build concatenated query
$concatsql = $select;
$concatsql .= $fromtable;
$concatsql .= $where;
$concatsql .= $Qsex;
$concatsql .= $QheightFt;
$concatsql .= $QheightIn;
$concatsql .= $Qneck;
$concatsql .= $Qchest;
$concatsql .= $Qwaist;
$concatsql .= $Qhips;
$concatsql .= $Qleg;
$concatsql .= $Qshoe;
$concatsql .= $Qdress;
$concatsql .= $Qhair;
$concatsql .= $Qeyes;
$concatsql .= $Qskin;
$concatsql .= $Qnationality;
$concatsql .= $orderby;
$concatsql .= $limit;
// make query
$result=@mysql_query($concatsql, $connection) or die (mysql_error() .
"BRBfrom query:/B$concatsql");
//detect amount of rows in result for display
$num_rows = mysql_num_rows($result);
//start building set of results to be echoed in browser
$contact_list= "UL";
while ($row = mysql_fetch_array($result)) {
$ref = $row['ref'];
$firstname = $row['firstname'];
$surname = $row['surname'];
$contact_list .="LIA HREF=\"show_record.php?ref=$ref\"$surname,
$firstname/A";
}
$contact_list .="/UL";
// add 10 to offset for next set of results
$offset +=10;
So, that all works for the first set of results but I do not know
where to
go from here. I am assuming the last sectionis the way to go to
increase the
offset value but I don't know what to do with it next!! I have been
scouring
