[PHP-DB] Select on Group

[Karl DeSaulniers]

Hello All,
Hoping someone can help me with this query.

I want to select some custom fields from my database that are part of a group 
of custom fields.
There are several of these groups. I want to (in one sql statement) grab these 
fields, all of them
and have them grouped in the results like so.


$custom_fields = array(
'Group1' => array(
'field_ID' => '1', 
'field_name' => 'myAddressField',
'filed_slug' => 'my-address-field'
),
'Group2' => array(
'field_ID' => '2', 
'field_name' => 'myCityField',
'filed_slug' => 'my-city-field'
),
'Group3' => array(
'field_ID' => '3', 
'field_name' => 'myStateField',
'filed_slug' => 'my-state-field'
)
)

Here is the clincher... not all the info is in the same table.
This is what I am doing currently and it works, however I would like to 
eliminate calling the database in a foreach loop as well as multiple times to 
get my results.

[code]

$FieldGroups = $wpdb->get_results("SELECT DISTINCT Field_Group FROM 
".table_name1."");

foreach($FieldGroups as $i=>$FieldGroup) {
$field_group = stripslashes_deep($FieldGroup->Field_Group);

$SQL = "SELECT ft.*, mt.* 
FROM ". table_name1." ft 
LEFT JOIN ". table_name2." mt 
ON mt.Field_ID = ft.Field_ID 
WHERE ft.Field_Group='%s' AND mt.Page_ID=%d AND 
ft.Field_Display='%s' 
ORDER BY ft.Field_ID ASC";  
$Fields = $wpdb->get_results($wpdb->prepare($SQL, $field_group, 
$Page_ID, $display));
}

[end code]

How can I combine these into one query that fills the result array the way 
described above?
I thought of a SELECT inside a SELECT, but my php foo is a little under trained 
at the moment.
Not sure how to do such and achieve the results I am after. Any help would be 
appreciated. 

TIA

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

Re: [PHP-DB] Select from multiple tables

[Kranthi Krishna]

I am a little confused

> It seems to me that you are mixing two semantically different things
I agree. But since both of them are attributes of school so I have to.

>  so you get a cartesian join over these tables(within the restrictions in the 
> where clause)
If I am not mistaken INNER JOIN is Cartesian join with where clause

> Still, you want to lump them together in the same result set.
You misunderstood me. I never said that I want a single row set. The
following query

SELECT s.Title, b.Board_id, t.type FROM (SELECT Title FROM school
where School_id = 1698) s, (SELECT  GROUP_CONCAT(Board_id) AS Board_id
FROM board_entries WHERE School_id = 1698) b, (SELECT
GROUP_CONCAT(type) AS type FROM schooltypeentries WHERE schoolid =
1698) t

returns me a single row set. But I am aware that this is not practical

All I want to do is to restore an object with multiple one-to-many relations

Kranthi.
http://goo.gl/e6t3



On 1 March 2012 21:47, Carl Michael Skog  wrote:
> -- Vidarebefordrat meddelande --
> Från: Carl Michael Skog 
> Datum: 1 mars 2012 17:12
> Ämne: Re: [PHP-DB] Select from multiple tables
> Till: Kranthi Krishna 
>
>
> It seems to me that you are mixing two semantically different
> things(board_entries and schooltypeentries, both related to school).
> These have no relation to each other(at least no one shown here, so you get
> a cartesian join over these tables(within the restrictions in the where
> clause)).
>
> Still, you want to lump them together in the same result set.
>
> The question is why ?
>
> Den 1 mars 2012 06:46 skrev Kranthi Krishna :
>
> Hi all,
>>
>> SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s,
>> board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND
>> b.School_id = 1698 AND t.schoolid = 1698
>>
>> this SQL query gives me
>>
>> Kendriya Vidyalaya      15      Kick Boxing
>> Kendriya Vidyalaya      15      Karate
>> Kendriya Vidyalaya      32      Kick Boxing
>> Kendriya Vidyalaya      32      Karate
>>
>> as I stated earlier.
>>
>> Now using php.net/array_search php.net/foreach and
>> php.net/mysql_fetch_assoc
>>
>> I can easily convert that into
>>
>> array
>>     name => 'Kendriya Vidyalaya'
>>     board_id => array
>>          1 => 15
>>          2 => 32
>>     type => array
>>          1 => 'Kick Boxing'
>>          2 => 'Karate'
>>
>> I am wondering if there is a better way. For example if I am able to
>> get something like
>>
>> Kendriya Vidyalaya      15          Kick Boxing
>> NULL                    NULL    Karate
>> NULL                    32          NULL
>> NULL                    NULL    NULL
>>
>> I can use php.net/is_null instead of php.net/array_search
>>
>> I dont think this problem is specific to me. Please suggest some best
>> practices in this case.
>>
>> Kranthi.
>> http://goo.gl/e6t3
>>
>>
>>
>> On 1 March 2012 10:25, Karl DeSaulniers  wrote:
>> > Try DISTINCT
>> >
>> >
>> >
>> > On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote:
>> >
>> >> Dear Kranthi
>> >>
>> >> You have to be clear what you decide especially when you are getting
>> >> multiple rows. To get just a single row you can use LIMIT clause.
>> >>
>> >> But it would return only one row. Now you  have to decide which row.
>> >>
>> >> So i think you decide on what you require and see how can you uniquely
>> >> identify that row
>> >> 
>> >> regds
>> >> amit
>> >>
>> >> "The difference between fiction and reality? Fiction has to make sense."
>> >>
>> >>
>> >> On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna
>> >> wrote:
>> >>
>> >>> Hi,
>> >>>
>> >>> The examples I saw were regarding cartesian join not inner join.  I
>> >>> will read about inner joins. Also, the example i mentioned seems to be
>> >>> a mistake. Both school and type will not be similar at the same time
>> >>>
>> >>>
>> >>> Kranthi.
>> >>> http://goo.gl/e6t3
>> >>>
>> >>>
>> >>>
>> >>> On 1 March 2012 09:26, Kranthi Krishna  wrote:
>> >>>>
>> >>>> Hi,
>> >>>>
>> >>>> Thanks for the input. I have seen some tutorials on joins, they all
&

Fwd: [PHP-DB] Select from multiple tables

[Carl Michael Skog]

-- Vidarebefordrat meddelande --
Från: Carl Michael Skog 
Datum: 1 mars 2012 17:12
Ämne: Re: [PHP-DB] Select from multiple tables
Till: Kranthi Krishna 


It seems to me that you are mixing two semantically different
things(board_entries and schooltypeentries, both related to school).
These have no relation to each other(at least no one shown here, so you get
a cartesian join over these tables(within the restrictions in the where
clause)).

Still, you want to lump them together in the same result set.

The question is why ?

Den 1 mars 2012 06:46 skrev Kranthi Krishna :

Hi all,
>
> SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s,
> board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND
> b.School_id = 1698 AND t.schoolid = 1698
>
> this SQL query gives me
>
> Kendriya Vidyalaya  15  Kick Boxing
> Kendriya Vidyalaya  15  Karate
> Kendriya Vidyalaya  32  Kick Boxing
> Kendriya Vidyalaya  32  Karate
>
> as I stated earlier.
>
> Now using php.net/array_search php.net/foreach and
> php.net/mysql_fetch_assoc
>
> I can easily convert that into
>
> array
> name => 'Kendriya Vidyalaya'
> board_id => array
>  1 => 15
>  2 => 32
> type => array
>  1 => 'Kick Boxing'
>  2 => 'Karate'
>
> I am wondering if there is a better way. For example if I am able to
> get something like
>
> Kendriya Vidyalaya  15  Kick Boxing
> NULLNULLKarate
> NULL32  NULL
> NULLNULLNULL
>
> I can use php.net/is_null instead of php.net/array_search
>
> I dont think this problem is specific to me. Please suggest some best
> practices in this case.
>
> Kranthi.
> http://goo.gl/e6t3
>
>
>
> On 1 March 2012 10:25, Karl DeSaulniers  wrote:
> > Try DISTINCT
> >
> >
> >
> > On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote:
> >
> >> Dear Kranthi
> >>
> >> You have to be clear what you decide especially when you are getting
> >> multiple rows. To get just a single row you can use LIMIT clause.
> >>
> >> But it would return only one row. Now you  have to decide which row.
> >>
> >> So i think you decide on what you require and see how can you uniquely
> >> identify that row
> >> 
> >> regds
> >> amit
> >>
> >> "The difference between fiction and reality? Fiction has to make sense."
> >>
> >>
> >> On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna
> >> wrote:
> >>
> >>> Hi,
> >>>
> >>> The examples I saw were regarding cartesian join not inner join.  I
> >>> will read about inner joins. Also, the example i mentioned seems to be
> >>> a mistake. Both school and type will not be similar at the same time
> >>>
> >>>
> >>> Kranthi.
> >>> http://goo.gl/e6t3
> >>>
> >>>
> >>>
> >>> On 1 March 2012 09:26, Kranthi Krishna  wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> Thanks for the input. I have seen some tutorials on joins, they all
> >>>> suggest that MySql returns multiple rows
> >>>>
> >>>> For example
> >>>> --
> >>>> School | Board 1
> >>>> --
> >>>> School | Board 1
> >>>> -
> >>>>
> >>>> Now if I have another one-to-many relation
> >>>>
> >>>> ---
> >>>> School | Board 1 | Type 1
> >>>> ---
> >>>> School | Board 1 | Type 2
> >>>> ---
> >>>> School | Board 2 | Type 1
> >>>> ---
> >>>> School | Board 2 | Type 2
> >>>> 
> >>>>
> >>>> Using UNIQUE or something similar (like php.net/array_search ) causes
> >>>> problems when Type 1 = Type 2 etc.
> >>>>
> >>>> Kranthi.
> >>>> http://goo.gl/e6t3
> >>>>
> >>>>
> >>>>
> >>>> On 29 February 2012 19:43, Michael Stowe 
> wrote:
> >>>>>
> >>>>> Select table1.item1, table2.item1 from table1 in

Re: [PHP-DB] Select from multiple tables

[Karl DeSaulniers]

This is just a stab in the dark and may be in the wrong order. If it  
does not work I apologize.


SELECT s.Title, (SELECT DISTINCT b.Board_id), (SELECT DISTINCT  
t.type), (SELECT s.School_id AND

b.School_id AND t.schoolid AS id ) FROM school s,
board_entries b, schooltypeentries t  WHERE id = 1698

HTW,

Best,
Karl


On Feb 29, 2012, at 11:46 PM, Kranthi Krishna wrote:


Hi all,

SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s,
board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND
b.School_id = 1698 AND t.schoolid = 1698

this SQL query gives me

Kendriya Vidyalaya  15  Kick Boxing
Kendriya Vidyalaya  15  Karate
Kendriya Vidyalaya  32  Kick Boxing
Kendriya Vidyalaya  32  Karate

as I stated earlier.

Now using php.net/array_search php.net/foreach and php.net/ 
mysql_fetch_assoc


I can easily convert that into

array
name => 'Kendriya Vidyalaya'
board_id => array
 1 => 15
 2 => 32
type => array
 1 => 'Kick Boxing'
 2 => 'Karate'

I am wondering if there is a better way. For example if I am able to
get something like

Kendriya Vidyalaya  15  Kick Boxing
NULLNULLKarate
NULL32  NULL
NULLNULLNULL

I can use php.net/is_null instead of php.net/array_search

I dont think this problem is specific to me. Please suggest some best
practices in this case.

Kranthi.
http://goo.gl/e6t3



On 1 March 2012 10:25, Karl DeSaulniers  wrote:

Try DISTINCT



On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote:


Dear Kranthi

You have to be clear what you decide especially when you are getting
multiple rows. To get just a single row you can use LIMIT clause.

But it would return only one row. Now you  have to decide which row.

So i think you decide on what you require and see how can you  
uniquely

identify that row

regds
amit

"The difference between fiction and reality? Fiction has to make  
sense."



On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna
wrote:


Hi,

The examples I saw were regarding cartesian join not inner join.  I
will read about inner joins. Also, the example i mentioned seems  
to be
a mistake. Both school and type will not be similar at the same  
time



Kranthi.
http://goo.gl/e6t3



On 1 March 2012 09:26, Kranthi Krishna   
wrote:


Hi,

Thanks for the input. I have seen some tutorials on joins, they  
all

suggest that MySql returns multiple rows

For example
--
School | Board 1
--
School | Board 1
-

Now if I have another one-to-many relation

---
School | Board 1 | Type 1
---
School | Board 1 | Type 2
---
School | Board 2 | Type 1
---
School | Board 2 | Type 2


Using UNIQUE or something similar (like php.net/array_search )  
causes

problems when Type 1 = Type 2 etc.

Kranthi.
http://goo.gl/e6t3



On 29 February 2012 19:43, Michael Stowe   
wrote:


Select table1.item1, table2.item1 from table1 inner join table2  
on


table1.key = table2.foreignKey Where...



You can also utilize left and right join to get data if there  
isn't a


direct match (ie customer may not have ordered anything so you  
want to do

a
left join on orders as there may not be any order data but you  
still want

to get the customer info).



Hope that helps,
Mike



Sent from my iPhone

On Feb 29, 2012, at 8:01 AM, Kranthi Krishna >


wrote:




Hi all,

Say I have an object like

array
 schoolName => string
 board => array
  string
  string

I generally create  two MySql tables

schools: id PRIMARY KEY, SchoolName
boards: id FOREGIN KEY refers Table A(id), board

and then do two selects. The problem is that, the number of  
selects

increase as the number of one-to-many relationships increase.

Is there a better way to do this ? I have to extend an  
existing code

so I cannot use any libraries like doctrine

Kranthi.
http://goo.gl/e6t3

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




Karl DeSaulniers
Design Drumm
http://designdrumm.com



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select from multiple tables

[Kranthi Krishna]

Hi all,

SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s,
board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND
b.School_id = 1698 AND t.schoolid = 1698

this SQL query gives me

Kendriya Vidyalaya  15  Kick Boxing
Kendriya Vidyalaya  15  Karate
Kendriya Vidyalaya  32  Kick Boxing
Kendriya Vidyalaya  32  Karate

as I stated earlier.

Now using php.net/array_search php.net/foreach and php.net/mysql_fetch_assoc

I can easily convert that into

array
 name => 'Kendriya Vidyalaya'
 board_id => array
  1 => 15
  2 => 32
 type => array
  1 => 'Kick Boxing'
  2 => 'Karate'

I am wondering if there is a better way. For example if I am able to
get something like

Kendriya Vidyalaya  15  Kick Boxing
NULLNULLKarate
NULL32  NULL
NULLNULLNULL

I can use php.net/is_null instead of php.net/array_search

I dont think this problem is specific to me. Please suggest some best
practices in this case.

Kranthi.
http://goo.gl/e6t3



On 1 March 2012 10:25, Karl DeSaulniers  wrote:
> Try DISTINCT
>
>
>
> On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote:
>
>> Dear Kranthi
>>
>> You have to be clear what you decide especially when you are getting
>> multiple rows. To get just a single row you can use LIMIT clause.
>>
>> But it would return only one row. Now you  have to decide which row.
>>
>> So i think you decide on what you require and see how can you uniquely
>> identify that row
>> 
>> regds
>> amit
>>
>> "The difference between fiction and reality? Fiction has to make sense."
>>
>>
>> On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna
>> wrote:
>>
>>> Hi,
>>>
>>> The examples I saw were regarding cartesian join not inner join.  I
>>> will read about inner joins. Also, the example i mentioned seems to be
>>> a mistake. Both school and type will not be similar at the same time
>>>
>>>
>>> Kranthi.
>>> http://goo.gl/e6t3
>>>
>>>
>>>
>>> On 1 March 2012 09:26, Kranthi Krishna  wrote:

 Hi,

 Thanks for the input. I have seen some tutorials on joins, they all
 suggest that MySql returns multiple rows

 For example
 --
 School | Board 1
 --
 School | Board 1
 -

 Now if I have another one-to-many relation

 ---
 School | Board 1 | Type 1
 ---
 School | Board 1 | Type 2
 ---
 School | Board 2 | Type 1
 ---
 School | Board 2 | Type 2
 

 Using UNIQUE or something similar (like php.net/array_search ) causes
 problems when Type 1 = Type 2 etc.

 Kranthi.
 http://goo.gl/e6t3



 On 29 February 2012 19:43, Michael Stowe  wrote:
>
> Select table1.item1, table2.item1 from table1 inner join table2 on
>>>
>>> table1.key = table2.foreignKey Where...
>
>
> You can also utilize left and right join to get data if there isn't a
>>>
>>> direct match (ie customer may not have ordered anything so you want to do
>>> a
>>> left join on orders as there may not be any order data but you still want
>>> to get the customer info).
>
>
> Hope that helps,
> Mike
>
>
>
> Sent from my iPhone
>
> On Feb 29, 2012, at 8:01 AM, Kranthi Krishna 
>>>
>>> wrote:
>
>
>> Hi all,
>>
>> Say I have an object like
>>
>> array
>>  schoolName => string
>>  board => array
>>       string
>>       string
>>
>> I generally create  two MySql tables
>>
>> schools: id PRIMARY KEY, SchoolName
>> boards: id FOREGIN KEY refers Table A(id), board
>>
>> and then do two selects. The problem is that, the number of selects
>> increase as the number of one-to-many relationships increase.
>>
>> Is there a better way to do this ? I have to extend an existing code
>> so I cannot use any libraries like doctrine
>>
>> Kranthi.
>> http://goo.gl/e6t3
>>
>> --
>> 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
>>>
>>>
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
> --
> 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] Select from multiple tables

[Karl DeSaulniers]

Try DISTINCT


On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote:


Dear Kranthi

You have to be clear what you decide especially when you are getting
multiple rows. To get just a single row you can use LIMIT clause.

But it would return only one row. Now you  have to decide which row.

So i think you decide on what you require and see how can you uniquely
identify that row

regds
amit

"The difference between fiction and reality? Fiction has to make  
sense."



On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna  
wrote:



Hi,

The examples I saw were regarding cartesian join not inner join.  I
will read about inner joins. Also, the example i mentioned seems to  
be

a mistake. Both school and type will not be similar at the same time


Kranthi.
http://goo.gl/e6t3



On 1 March 2012 09:26, Kranthi Krishna  wrote:

Hi,

Thanks for the input. I have seen some tutorials on joins, they all
suggest that MySql returns multiple rows

For example
--
School | Board 1
--
School | Board 1
-

Now if I have another one-to-many relation

---
School | Board 1 | Type 1
---
School | Board 1 | Type 2
---
School | Board 2 | Type 1
---
School | Board 2 | Type 2


Using UNIQUE or something similar (like php.net/array_search )  
causes

problems when Type 1 = Type 2 etc.

Kranthi.
http://goo.gl/e6t3



On 29 February 2012 19:43, Michael Stowe   
wrote:

Select table1.item1, table2.item1 from table1 inner join table2 on

table1.key = table2.foreignKey Where...


You can also utilize left and right join to get data if there  
isn't a
direct match (ie customer may not have ordered anything so you want  
to do a
left join on orders as there may not be any order data but you  
still want

to get the customer info).


Hope that helps,
Mike



Sent from my iPhone

On Feb 29, 2012, at 8:01 AM, Kranthi Krishna 

wrote:



Hi all,

Say I have an object like

array
  schoolName => string
  board => array
   string
   string

I generally create  two MySql tables

schools: id PRIMARY KEY, SchoolName
boards: id FOREGIN KEY refers Table A(id), board

and then do two selects. The problem is that, the number of  
selects

increase as the number of one-to-many relationships increase.

Is there a better way to do this ? I have to extend an existing  
code

so I cannot use any libraries like doctrine

Kranthi.
http://goo.gl/e6t3

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




Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select from multiple tables

[Amit Tandon]

Dear Kranthi

You have to be clear what you decide especially when you are getting
multiple rows. To get just a single row you can use LIMIT clause.

But it would return only one row. Now you  have to decide which row.

So i think you decide on what you require and see how can you uniquely
identify that row

regds
amit

"The difference between fiction and reality? Fiction has to make sense."


On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna wrote:

> Hi,
>
> The examples I saw were regarding cartesian join not inner join.  I
> will read about inner joins. Also, the example i mentioned seems to be
> a mistake. Both school and type will not be similar at the same time
>
>
> Kranthi.
> http://goo.gl/e6t3
>
>
>
> On 1 March 2012 09:26, Kranthi Krishna  wrote:
> > Hi,
> >
> > Thanks for the input. I have seen some tutorials on joins, they all
> > suggest that MySql returns multiple rows
> >
> > For example
> > --
> > School | Board 1
> > --
> > School | Board 1
> > -
> >
> > Now if I have another one-to-many relation
> >
> > ---
> > School | Board 1 | Type 1
> > ---
> > School | Board 1 | Type 2
> > ---
> > School | Board 2 | Type 1
> > ---
> > School | Board 2 | Type 2
> > 
> >
> > Using UNIQUE or something similar (like php.net/array_search ) causes
> > problems when Type 1 = Type 2 etc.
> >
> > Kranthi.
> > http://goo.gl/e6t3
> >
> >
> >
> > On 29 February 2012 19:43, Michael Stowe  wrote:
> >> Select table1.item1, table2.item1 from table1 inner join table2 on
> table1.key = table2.foreignKey Where...
> >>
> >> You can also utilize left and right join to get data if there isn't a
> direct match (ie customer may not have ordered anything so you want to do a
> left join on orders as there may not be any order data but you still want
> to get the customer info).
> >>
> >> Hope that helps,
> >> Mike
> >>
> >>
> >>
> >> Sent from my iPhone
> >>
> >> On Feb 29, 2012, at 8:01 AM, Kranthi Krishna 
> wrote:
> >>
> >>> Hi all,
> >>>
> >>> Say I have an object like
> >>>
> >>> array
> >>>schoolName => string
> >>>board => array
> >>> string
> >>> string
> >>>
> >>> I generally create  two MySql tables
> >>>
> >>> schools: id PRIMARY KEY, SchoolName
> >>> boards: id FOREGIN KEY refers Table A(id), board
> >>>
> >>> and then do two selects. The problem is that, the number of selects
> >>> increase as the number of one-to-many relationships increase.
> >>>
> >>> Is there a better way to do this ? I have to extend an existing code
> >>> so I cannot use any libraries like doctrine
> >>>
> >>> Kranthi.
> >>> http://goo.gl/e6t3
> >>>
> >>> --
> >>> 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] Select from multiple tables

[Kranthi Krishna]

Hi,

The examples I saw were regarding cartesian join not inner join.  I
will read about inner joins. Also, the example i mentioned seems to be
a mistake. Both school and type will not be similar at the same time


Kranthi.
http://goo.gl/e6t3



On 1 March 2012 09:26, Kranthi Krishna  wrote:
> Hi,
>
> Thanks for the input. I have seen some tutorials on joins, they all
> suggest that MySql returns multiple rows
>
> For example
> --
> School | Board 1
> --
> School | Board 1
> -
>
> Now if I have another one-to-many relation
>
> ---
> School | Board 1 | Type 1
> ---
> School | Board 1 | Type 2
> ---
> School | Board 2 | Type 1
> ---
> School | Board 2 | Type 2
> 
>
> Using UNIQUE or something similar (like php.net/array_search ) causes
> problems when Type 1 = Type 2 etc.
>
> Kranthi.
> http://goo.gl/e6t3
>
>
>
> On 29 February 2012 19:43, Michael Stowe  wrote:
>> Select table1.item1, table2.item1 from table1 inner join table2 on 
>> table1.key = table2.foreignKey Where...
>>
>> You can also utilize left and right join to get data if there isn't a direct 
>> match (ie customer may not have ordered anything so you want to do a left 
>> join on orders as there may not be any order data but you still want to get 
>> the customer info).
>>
>> Hope that helps,
>> Mike
>>
>>
>>
>> Sent from my iPhone
>>
>> On Feb 29, 2012, at 8:01 AM, Kranthi Krishna  wrote:
>>
>>> Hi all,
>>>
>>> Say I have an object like
>>>
>>> array
>>>    schoolName => string
>>>    board => array
>>>         string
>>>         string
>>>
>>> I generally create  two MySql tables
>>>
>>> schools: id PRIMARY KEY, SchoolName
>>> boards: id FOREGIN KEY refers Table A(id), board
>>>
>>> and then do two selects. The problem is that, the number of selects
>>> increase as the number of one-to-many relationships increase.
>>>
>>> Is there a better way to do this ? I have to extend an existing code
>>> so I cannot use any libraries like doctrine
>>>
>>> Kranthi.
>>> http://goo.gl/e6t3
>>>
>>> --
>>> 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] Select from multiple tables

[Kranthi Krishna]

Hi,

Thanks for the input. I have seen some tutorials on joins, they all
suggest that MySql returns multiple rows

For example
--
School | Board 1
--
School | Board 1
-

Now if I have another one-to-many relation

---
School | Board 1 | Type 1
---
School | Board 1 | Type 2
---
School | Board 2 | Type 1
---
School | Board 2 | Type 2


Using UNIQUE or something similar (like php.net/array_search ) causes
problems when Type 1 = Type 2 etc.

Kranthi.
http://goo.gl/e6t3



On 29 February 2012 19:43, Michael Stowe  wrote:
> Select table1.item1, table2.item1 from table1 inner join table2 on table1.key 
> = table2.foreignKey Where...
>
> You can also utilize left and right join to get data if there isn't a direct 
> match (ie customer may not have ordered anything so you want to do a left 
> join on orders as there may not be any order data but you still want to get 
> the customer info).
>
> Hope that helps,
> Mike
>
>
>
> Sent from my iPhone
>
> On Feb 29, 2012, at 8:01 AM, Kranthi Krishna  wrote:
>
>> Hi all,
>>
>> Say I have an object like
>>
>> array
>>    schoolName => string
>>    board => array
>>         string
>>         string
>>
>> I generally create  two MySql tables
>>
>> schools: id PRIMARY KEY, SchoolName
>> boards: id FOREGIN KEY refers Table A(id), board
>>
>> and then do two selects. The problem is that, the number of selects
>> increase as the number of one-to-many relationships increase.
>>
>> Is there a better way to do this ? I have to extend an existing code
>> so I cannot use any libraries like doctrine
>>
>> Kranthi.
>> http://goo.gl/e6t3
>>
>> --
>> 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] Select from multiple tables

[Matijn Woudt]

On Wed, Feb 29, 2012 at 3:01 PM, Kranthi Krishna  wrote:
> Hi all,
>
> Say I have an object like
>
> array
>    schoolName => string
>    board => array
>         string
>         string
>
> I generally create  two MySql tables
>
> schools: id PRIMARY KEY, SchoolName
> boards: id FOREGIN KEY refers Table A(id), board
>
> and then do two selects. The problem is that, the number of selects
> increase as the number of one-to-many relationships increase.
>
> Is there a better way to do this ? I have to extend an existing code
> so I cannot use any libraries like doctrine
>
> Kranthi.
> http://goo.gl/e6t3

You should look up at SQL joins. They will do what you want.

- Matijn

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Select from multiple tables

[Kranthi Krishna]

Hi all,

Say I have an object like

array
schoolName => string
board => array
 string
 string

I generally create  two MySql tables

schools: id PRIMARY KEY, SchoolName
boards: id FOREGIN KEY refers Table A(id), board

and then do two selects. The problem is that, the number of selects
increase as the number of one-to-many relationships increase.

Is there a better way to do this ? I have to extend an existing code
so I cannot use any libraries like doctrine

Kranthi.
http://goo.gl/e6t3

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT

[tamouse mailing lists]

On Fri, Oct 21, 2011 at 2:09 AM, Ford, Mike  wrote:
>> -Original Message-
>> From: tamouse mailing lists [mailto:tamouse.li...@gmail.com]
>> Sent: 20 October 2011 21:37
>>
>> On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike 
>> wrote:
>> >> -Original Message-
>> >> From: Ron Piggott [mailto:ron.pigg...@actsministries.org]
>> >> Sent: 17 October 2011 18:38
>> >>
>> >> What I am storing in the table is the start month # (1 to 12) and
>> >> day # (1 to 31) and then the finishing month # (1 to 12) and the
>> >> finishing day # (1 to 31)
>> >>
>> >
>> > This is a little bit of a tricky one, as you have to consider both
>> > start_month and end_month as special cases - so you need a three-
>> part
>> > conditional, for the start month, the end month, and the months in
>> > between. Something like this:
>> >
>> > SELECT * FROM `introduction_messages`
>> >  WHERE (month>`start_month` AND month<`end_month`)
>> >       OR (month=`start_month AND day>=`start_day`)
>> >       OR (month=`end_month` AND day<=`end_day`);
>>
>> This still suffers from the problem in Jim's offer -- wrap of year
>> and
>> wrap of month
>
> Look again. Month wrap *is* handled by the specific tests for start_month
> and end_month.

Hmm -- yes, you are right -- it does handle the month wrap problem okay.

> As to year-wrap, Ron's original post said:
>
>> >> ... The reason I didn’t use ‘DATE’ is because the same message
>> >> will be displayed year after year, depending on the date range.
>
> so I didn't bother about year-wrap, assuming he would include a range
> with start_date of 1/1 and another with end_date of 31/12.

So you are saying it can be easily worked around if there is a
particular case that is supposed to wrap over the end of the year and
simply include the item twice: one starting on Jan 1 and the
other one ending on Dec 31. (I'm not sure if that's what you meant
above.)

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SELECT

[Ford, Mike]

> -Original Message-
> From: tamouse mailing lists [mailto:tamouse.li...@gmail.com]
> Sent: 20 October 2011 21:37
> 
> On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike 
> wrote:
> >> -Original Message-
> >> From: Ron Piggott [mailto:ron.pigg...@actsministries.org]
> >> Sent: 17 October 2011 18:38
> >>
> >> What I am storing in the table is the start month # (1 to 12) and
> >> day # (1 to 31) and then the finishing month # (1 to 12) and the
> >> finishing day # (1 to 31)
> >>
> >
> > This is a little bit of a tricky one, as you have to consider both
> > start_month and end_month as special cases - so you need a three-
> part
> > conditional, for the start month, the end month, and the months in
> > between. Something like this:
> >
> > SELECT * FROM `introduction_messages`
> >  WHERE (month>`start_month` AND month<`end_month`)
> >       OR (month=`start_month AND day>=`start_day`)
> >       OR (month=`end_month` AND day<=`end_day`);
> 
> This still suffers from the problem in Jim's offer -- wrap of year
> and
> wrap of month

Look again. Month wrap *is* handled by the specific tests for start_month
and end_month.

As to year-wrap, Ron's original post said:

> >> ... The reason I didn’t use ‘DATE’ is because the same message
> >> will be displayed year after year, depending on the date range.

so I didn't bother about year-wrap, assuming he would include a range
with start_date of 1/1 and another with end_date of 31/12.

Cheers!

Mike

-- 
Mike Ford,
Electronic Information Developer, Libraries and Learning Innovation,  
Portland PD507, City Campus, Leeds Metropolitan University,
Portland Way, LEEDS,  LS1 3HE,  United Kingdom 
E: m.f...@leedsmet.ac.uk T: +44 113 812 4730


 But in the
case of years actually mattering then, yes, the above would not work


To view the terms under which this email is distributed, please go to 
http://disclaimer.leedsmet.ac.uk/email.htm

Re: [PHP-DB] SELECT

[tamouse mailing lists]

On Thu, Oct 20, 2011 at 3:36 PM, tamouse mailing lists
 wrote:
> On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike  wrote:
>>> -Original Message-
>>> From: Ron Piggott [mailto:ron.pigg...@actsministries.org]
>>> Sent: 17 October 2011 18:38
>>>
>>> I need help creating a mySQL query that will select the correct
>>> introduction message for a website I am making.  The way I have
>>> designed the table I can’t wrap my mind around the SELECT query that
>>> will deal with the day # of the month.
>>>
>>> The part of the SELECT syntax I am struggling with is when the
>>> introduction message is to change mid month.  The reason I am
>>> struggling with this is because I haven’t used ‘DATE’ for the column
>>> type.  The reason I didn’t use ‘DATE’ is because the same message
>>> will be displayed year after year, depending on the date range.
>>>
>>> What I am storing in the table is the start month # (1 to 12) and
>>> day # (1 to 31) and then the finishing month # (1 to 12) and the
>>> finishing day # (1 to 31)
>>>
>>
>> This is a little bit of a tricky one, as you have to consider both
>> start_month and end_month as special cases - so you need a three-part
>> conditional, for the start month, the end month, and the months in
>> between. Something like this:
>>
>> SELECT * FROM `introduction_messages`
>>  WHERE (month>`start_month` AND month<`end_month`)
>>       OR (month=`start_month AND day>=`start_day`)
>>       OR (month=`end_month` AND day<=`end_day`);
>
> This still suffers from the problem in Jim's offer -- wrap of year and
> wrap of month
>
> This might be best handled in a stored procedure, converting the
> values stored in the table to dates to do the comparison with in the
> where clause.
>

In thinking further on this, the OP might consider this problem as
well -- it is going to be difficult to determine the correct response
if all that is stored is the start and ending month and day of month
in the case where the desired time stretch wraps over to the new year.
When your start month is 12 and your end month is 1, what do you
expect to happen?

It can't generally be solved by using current year for the start and
current year + 1 for the end. For example, you may want to have
something start at current year, month=6 and end as next year,
month=8, so simply checking if end month < start month won't give you
the ability to discern if you've wrapped the year. (I realize this may
not be the OP's case, but it is still an issue if seeking a general
solution.)

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT

[tamouse mailing lists]

On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike  wrote:
>> -Original Message-
>> From: Ron Piggott [mailto:ron.pigg...@actsministries.org]
>> Sent: 17 October 2011 18:38
>>
>> I need help creating a mySQL query that will select the correct
>> introduction message for a website I am making.  The way I have
>> designed the table I can’t wrap my mind around the SELECT query that
>> will deal with the day # of the month.
>>
>> The part of the SELECT syntax I am struggling with is when the
>> introduction message is to change mid month.  The reason I am
>> struggling with this is because I haven’t used ‘DATE’ for the column
>> type.  The reason I didn’t use ‘DATE’ is because the same message
>> will be displayed year after year, depending on the date range.
>>
>> What I am storing in the table is the start month # (1 to 12) and
>> day # (1 to 31) and then the finishing month # (1 to 12) and the
>> finishing day # (1 to 31)
>>
>
> This is a little bit of a tricky one, as you have to consider both
> start_month and end_month as special cases - so you need a three-part
> conditional, for the start month, the end month, and the months in
> between. Something like this:
>
> SELECT * FROM `introduction_messages`
>  WHERE (month>`start_month` AND month<`end_month`)
>       OR (month=`start_month AND day>=`start_day`)
>       OR (month=`end_month` AND day<=`end_day`);

This still suffers from the problem in Jim's offer -- wrap of year and
wrap of month

This might be best handled in a stored procedure, converting the
values stored in the table to dates to do the comparison with in the
where clause.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT

[Jim Giner]

Ron - Mike here is correct.  I gave you a start, but it had a problem with 
it.  Hope I didn't have you running around too much. 



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SELECT

[Ford, Mike]

> -Original Message-
> From: Ron Piggott [mailto:ron.pigg...@actsministries.org]
> Sent: 17 October 2011 18:38
> 
> I need help creating a mySQL query that will select the correct
> introduction message for a website I am making.  The way I have
> designed the table I can’t wrap my mind around the SELECT query that
> will deal with the day # of the month.
> 
> The part of the SELECT syntax I am struggling with is when the
> introduction message is to change mid month.  The reason I am
> struggling with this is because I haven’t used ‘DATE’ for the column
> type.  The reason I didn’t use ‘DATE’ is because the same message
> will be displayed year after year, depending on the date range.
> 
> What I am storing in the table is the start month # (1 to 12) and
> day # (1 to 31) and then the finishing month # (1 to 12) and the
> finishing day # (1 to 31)
> 

This is a little bit of a tricky one, as you have to consider both
start_month and end_month as special cases - so you need a three-part
conditional, for the start month, the end month, and the months in
between. Something like this:

SELECT * FROM `introduction_messages`
 WHERE (month>`start_month` AND month<`end_month`)
   OR (month=`start_month AND day>=`start_day`)
   OR (month=`end_month` AND day<=`end_day`);

Cheers!

Mike

-- 
Mike Ford,
Electronic Information Developer, Libraries and Learning Innovation,  
Portland PD507, City Campus, Leeds Metropolitan University,
Portland Way, LEEDS,  LS1 3HE,  United Kingdom 
E: m.f...@leedsmet.ac.uk T: +44 113 812 4730




To view the terms under which this email is distributed, please go to 
http://disclaimer.leedsmet.ac.uk/email.htm

Re: [PHP-DB] SELECT

[Amit Tandon]

Dear Ron

If only day is required u could add another day condition in the where
clause e.g. month(current_date) between and day(current_date) between. i
think u require something more than this. So could u pls explain your
requirement in a little more detail say what would be the output of the
query given by u. When would u consider the start date/month and when the
end one.

regds
amit

"The difference between fiction and reality? Fiction has to make sense."


On Mon, Oct 17, 2011 at 11:08 PM, Ron Piggott <
ron.pigg...@actsministries.org> wrote:

>
> I need help creating a mySQL query that will select the correct
> introduction message for a website I am making.  The way I have designed the
> table I can’t wrap my mind around the SELECT query that will deal with the
> day # of the month.
>
> The part of the SELECT syntax I am struggling with is when the introduction
> message is to change mid month.  The reason I am struggling with this is
> because I haven’t used ‘DATE’ for the column type.  The reason I didn’t use
> ‘DATE’ is because the same message will be displayed year after year,
> depending on the date range.
>
> What I am storing in the table is the start month # (1 to 12) and day # (1
> to 31) and then the finishing month # (1 to 12) and the finishing day # (1
> to 31)
>
> Table structure for table `introduction_messages`
> --
>
> CREATE TABLE IF NOT EXISTS `introduction_messages` (
>  `reference` int(2) NOT NULL AUTO_INCREMENT,
>  `start_month` int(2) NOT NULL,
>  `start_day` int(2) NOT NULL,
>  `end_month` int(2) NOT NULL,
>  `end_day` int(2) NOT NULL,
>  `theme` varchar(100) NOT NULL,
>  `message` longtext NOT NULL,
>  PRIMARY KEY (`reference`)
> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
>
> My query so far is:
>
> SELECT * FROM `introduction_messages` WHERE 11 BETWEEN `start_month` AND
> `end_month`
>
> 11 is for November.  2 rows have been selected:
>
> Row #1:
> `start_month` 9
> `start_day` 16
> `end_month` 11
> `end_day` 15
>
> Row #2:
> `start_month` 11
> `start_day` 16
> `end_month` 12
> `end_day` 10
>
> How do I modify the query to incorporate the day #?
>
> Ron
>
>
>
>
> www.TheVerseOfTheDay.info
>

[PHP-DB] SELECT

[Ron Piggott]

I need help creating a mySQL query that will select the correct introduction 
message for a website I am making.  The way I have designed the table I can’t 
wrap my mind around the SELECT query that will deal with the day # of the 
month.  

The part of the SELECT syntax I am struggling with is when the introduction 
message is to change mid month.  The reason I am struggling with this is 
because I haven’t used ‘DATE’ for the column type.  The reason I didn’t use 
‘DATE’ is because the same message will be displayed year after year, depending 
on the date range.  

What I am storing in the table is the start month # (1 to 12) and day # (1 to 
31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31)

Table structure for table `introduction_messages`
--

CREATE TABLE IF NOT EXISTS `introduction_messages` (
  `reference` int(2) NOT NULL AUTO_INCREMENT,
  `start_month` int(2) NOT NULL,
  `start_day` int(2) NOT NULL,
  `end_month` int(2) NOT NULL,
  `end_day` int(2) NOT NULL,
  `theme` varchar(100) NOT NULL,
  `message` longtext NOT NULL,
  PRIMARY KEY (`reference`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

My query so far is:

SELECT * FROM `introduction_messages` WHERE 11 BETWEEN `start_month` AND 
`end_month`

11 is for November.  2 rows have been selected:

Row #1:
`start_month` 9
`start_day` 16
`end_month` 11
`end_day` 15

Row #2:
`start_month` 11
`start_day` 16
`end_month` 12
`end_day` 10

How do I modify the query to incorporate the day #?

Ron




www.TheVerseOfTheDay.info 

Re: [PHP-DB] SELECT syntax

[Karl DeSaulniers]

Heh,
Thanks Karthik. Not my post.. :)
But your solution looks dead on..

Here you go Ron. Try this one.

Best,
Karl


On Oct 13, 2011, at 2:42 AM, Karthik S wrote:


Try this,

select
CASE answer
 when 1 then trivia_answer_1
 when 2 then trivia_answer_2
 when 3 then trivia_answer_3
 when 4 then trivia_answer_4
END as trivia_answers
from bible_trivia_table

On Thu, Oct 13, 2011 at 1:02 PM, Karl DeSaulniers > wrote:

Or something like this?
SELECT * FROM `Bible_trivia` WHERE answer=`answer`;
Then match the results to trivia_answer_1 in php to see if correct.

if($trivia_answer_1 == $results) {
... do this
}

or a switch

switch ($results) {
   case $trivia_answer_1:
   ... do this
   case $trivia_answer_2
   ... do this

Best,
Karl


On Oct 12, 2011, at 11:04 PM, Amit Tandon wrote:

SELECT `trivia_answer_`answer`` FROM `Bible_trivia`

Karl DeSaulniers
Design Drumm
http://designdrumm.com



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




Karl DeSaulniers
Design Drumm
http://designdrumm.com

Re: [PHP-DB] SELECT syntax

[Karl DeSaulniers]

Or something like this?
SELECT * FROM `Bible_trivia` WHERE answer=`answer`;
Then match the results to trivia_answer_1 in php to see if correct.

if($trivia_answer_1 == $results) {
... do this
}

or a switch

switch ($results) {
case $trivia_answer_1:
... do this
case $trivia_answer_2
... do this

Best,
Karl

On Oct 12, 2011, at 11:04 PM, Amit Tandon wrote:


SELECT `trivia_answer_`answer`` FROM `Bible_trivia`


Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT syntax

[Amit Tandon]

another 
example

regds
amit

"The difference between fiction and reality? Fiction has to make sense."


On Thu, Oct 13, 2011 at 9:34 AM, Amit Tandon  wrote:

> select 
> case 
> works in mysql also
> 
> regds
> amit
>
> "The difference between fiction and reality? Fiction has to make sense."
>
>
>
> On Thu, Oct 13, 2011 at 3:26 AM, Jack van Zanen  wrote:
>
>> Hi
>>
>> In Oracle (and maybe others) you can use
>>
>>
>> select case
>> when answer=1
>> then trivia_answer_1
>> when answer=2
>> then trivia_answer_2
>> when answer=3
>> then trivia_answer_3
>> when answer=4
>> then trivia_answer_4
>> else null
>> end answer
>> from bible_trivia_table
>> OR
>>
>> You can select all of them and process in PHP, should not be too hard to
>> come up with a couple of lines of code to display only 1 variable  based
>> on
>> the value of variable 5. Overhead should be pretty minimal as well
>> You'll be writing something to display a value anyway
>>
>>
>> Jack van Zanen
>>
>> -
>> This e-mail and any attachments may contain confidential material for the
>> sole use of the intended recipient. If you are not the intended recipient,
>> please be aware that any disclosure, copying, distribution or use of this
>> e-mail or any attachment is prohibited. If you have received this e-mail
>> in
>> error, please contact the sender and delete all copies.
>> Thank you for your cooperation
>>
>>
>> On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott <
>> ron.pigg...@actsministries.org
>> > wrote:
>>
>> >
>> > In my Bible_Trivia table I have the columns
>> >
>> > `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`,
>> `trivia_answer_4`,
>> > `answer`
>> >
>> > `answer` is an integer always with a value of 1 to 4. Is there a way to
>> use
>> > the value of `answer` to only select the correct trivia answer?
>> >
>> > This doesn’t work, but this is the idea I am trying to achieve:
>> >
>> > SELECT `trivia_answer_`answer`` FROM `Bible_trivia`
>> >
>> > Thanks in advance,
>> >
>> > Ron
>> >
>> >
>> >
>> > www.TheVerseOfTheDay.info 
>> >
>>
>
>

Re: [PHP-DB] SELECT syntax

[Amit Tandon]

select case
works in mysql also

regds
amit

"The difference between fiction and reality? Fiction has to make sense."


On Thu, Oct 13, 2011 at 3:26 AM, Jack van Zanen  wrote:

> Hi
>
> In Oracle (and maybe others) you can use
>
>
> select case
> when answer=1
> then trivia_answer_1
> when answer=2
> then trivia_answer_2
> when answer=3
> then trivia_answer_3
> when answer=4
> then trivia_answer_4
> else null
> end answer
> from bible_trivia_table
> OR
>
> You can select all of them and process in PHP, should not be too hard to
> come up with a couple of lines of code to display only 1 variable  based on
> the value of variable 5. Overhead should be pretty minimal as well
> You'll be writing something to display a value anyway
>
>
> Jack van Zanen
>
> -
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>
>
> On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott <
> ron.pigg...@actsministries.org
> > wrote:
>
> >
> > In my Bible_Trivia table I have the columns
> >
> > `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`,
> `trivia_answer_4`,
> > `answer`
> >
> > `answer` is an integer always with a value of 1 to 4. Is there a way to
> use
> > the value of `answer` to only select the correct trivia answer?
> >
> > This doesn’t work, but this is the idea I am trying to achieve:
> >
> > SELECT `trivia_answer_`answer`` FROM `Bible_trivia`
> >
> > Thanks in advance,
> >
> > Ron
> >
> >
> >
> > www.TheVerseOfTheDay.info 
> >
>

Re: [PHP-DB] SELECT syntax

[Jack van Zanen]

Hi

In Oracle (and maybe others) you can use


select case
when answer=1
then trivia_answer_1
when answer=2
then trivia_answer_2
when answer=3
then trivia_answer_3
when answer=4
then trivia_answer_4
else null
end answer
from bible_trivia_table
OR

You can select all of them and process in PHP, should not be too hard to
come up with a couple of lines of code to display only 1 variable  based on
the value of variable 5. Overhead should be pretty minimal as well
You'll be writing something to display a value anyway


Jack van Zanen

-
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation


On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott  wrote:

>
> In my Bible_Trivia table I have the columns
>
> `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`,
> `answer`
>
> `answer` is an integer always with a value of 1 to 4. Is there a way to use
> the value of `answer` to only select the correct trivia answer?
>
> This doesn’t work, but this is the idea I am trying to achieve:
>
> SELECT `trivia_answer_`answer`` FROM `Bible_trivia`
>
> Thanks in advance,
>
> Ron
>
>
>
> www.TheVerseOfTheDay.info 
>

RE: [PHP-DB] SELECT syntax

[Toby Hart Dyke]

Not terribly elegant, but this should work:

SELECT `trivia_answer_1` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=1
UNION
SELECT `trivia_answer_2` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=2
UNION
SELECT `trivia_answer_3` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=3
UNION
SELECT `trivia_answer_4` AS `trivia_answer` FROM `Bible_trivia` WHERE 
`answer`=4;

I have to say that it's likely that your design may not be the most optimal. 
What happens if you want 5 answers? Or 6?

  Toby


-Original Message-
From: Ron Piggott [mailto:ron.pigg...@actsministries.org] 
Sent: Wednesday, October 12, 2011 3:25 PM
To: php-db@lists.php.net
Subject: [PHP-DB] SELECT syntax


In my Bible_Trivia table I have the columns

`trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, 
`answer`

`answer` is an integer always with a value of 1 to 4. Is there a way to use the 
value of `answer` to only select the correct trivia answer?

This doesn’t work, but this is the idea I am trying to achieve:

SELECT `trivia_answer_`answer`` FROM `Bible_trivia`

Thanks in advance,

Ron



www.TheVerseOfTheDay.info 


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT syntax

[Ron Piggott]

In my Bible_Trivia table I have the columns

`trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, 
`answer`

`answer` is an integer always with a value of 1 to 4. Is there a way to use the 
value of `answer` to only select the correct trivia answer?

This doesn’t work, but this is the idea I am trying to achieve:

SELECT `trivia_answer_`answer`` FROM `Bible_trivia`

Thanks in advance,

Ron



www.TheVerseOfTheDay.info 

Re: [PHP-DB] SELECT online store discount %

[Ron Piggott]

A variety of if’s and Greatest in conjunction to mySQL math works!

SELECT (
(
GREATEST( IF( (

SELECT 10 AS discount
FROM `paypal_payment_info_sample`
WHERE `datecreation` >= DATE_SUB( NOW( ) , INTERVAL 4
MONTH )
AND `buyer_email` = '$client_email'
AND `paymentstatus` = 'Completed'
LIMIT 1 ) , 10, 0 ) , IF( (

SELECT 5 AS discount
FROM `paypal_payment_info_sample`
WHERE `datecreation`
BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR )
AND DATE_SUB( NOW( ) , INTERVAL 4
MONTH )
AND `buyer_email` = '$client_email'
AND `paymentstatus` = 'Completed'
LIMIT 1 ) , 5, 0
)
)
) + ( IF( (

SELECT 10 AS discount
FROM `subscriber_details`
WHERE `email` = '$client_email'
LIMIT 1
), 10, 0 ) )
) AS discount_percentage

The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info  


From: Amit Tandon 
Sent: Monday, August 22, 2011 5:45 AM
To: Ron Piggott 
Cc: php-db@lists.php.net 
Subject: Re: [PHP-DB] SELECT online store discount %

Ron

Have u thought of CASE (in SELECT). Remebber their is some syntactical 
difference in "CASE" for SELECT and "CASE" in procedures

regds
amit

"The difference between fiction and reality? Fiction has to make sense."



On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott  
wrote:


  I am trying to write a database query that determine the customer loyalty 
discount for an online store.  I am wondering if there is a way of doing this 
as 1 query, instead of multiple and using PHP to do the math?

  - I want to offer a 10% discount if the person is a subscriber

  SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = 
'$client_email' LIMIT 1

  - I also want to offer a customer loyalty discount:

  10% if this is a purchase within 4 months of the previous purchase,

  SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` 
>= DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' 
LIMIT 1

  - OR 5% if the most recent previous purchase is between 4 months and 1 year 
ago.

  SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` 
BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 
MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1

  The discounts possibilities would be:
  - 20% (a subscriber with a purchase within the past 4 months)
  - 15% (a subscriber with a purchase between 4 months and a year ago)
  - 10% (for being a subscriber)
  - 10% (for a purchase made within the past 4 months)
  - 5% (for a purchase made between 4 months and a year ago)

  Is there a way to do this all within the context of 1 query?

  Ron

  The Verse of the Day
  “Encouragement from God’s Word”
  http://www.TheVerseOfTheDay.info

Re: [PHP-DB] SELECT online store discount %

[Amit Tandon]

Ron

Have u thought of CASE (in
SELECT).
Remebber their is some syntactical difference in "CASE" for SELECT and
"CASE" in procedures

regds
amit

"The difference between fiction and reality? Fiction has to make sense."


On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott <
ron.pigg...@actsministries.org> wrote:

>
> I am trying to write a database query that determine the customer loyalty
> discount for an online store.  I am wondering if there is a way of doing
> this as 1 query, instead of multiple and using PHP to do the math?
>
> - I want to offer a 10% discount if the person is a subscriber
>
> SELECT 10 AS discount FROM `subscriber_details` WHERE `email` =
> '$client_email' LIMIT 1
>
> - I also want to offer a customer loyalty discount:
>
> 10% if this is a purchase within 4 months of the previous purchase,
>
> SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE
> `datecreation` >= DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus`
> = 'Completed' LIMIT 1
>
> - OR 5% if the most recent previous purchase is between 4 months and 1 year
> ago.
>
> SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation`
> BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL
> 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1
>
> The discounts possibilities would be:
> - 20% (a subscriber with a purchase within the past 4 months)
> - 15% (a subscriber with a purchase between 4 months and a year ago)
> - 10% (for being a subscriber)
> - 10% (for a purchase made within the past 4 months)
> - 5% (for a purchase made between 4 months and a year ago)
>
> Is there a way to do this all within the context of 1 query?
>
> Ron
>
> The Verse of the Day
> “Encouragement from God’s Word”
> http://www.TheVerseOfTheDay.info
>

[PHP-DB] SELECT online store discount %

[Ron Piggott]

I am trying to write a database query that determine the customer loyalty 
discount for an online store.  I am wondering if there is a way of doing this 
as 1 query, instead of multiple and using PHP to do the math?

- I want to offer a 10% discount if the person is a subscriber

SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' 
LIMIT 1

- I also want to offer a customer loyalty discount: 

10% if this is a purchase within 4 months of the previous purchase, 

SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` >= 
DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1

- OR 5% if the most recent previous purchase is between 4 months and 1 year ago.

SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` 
BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 
MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1

The discounts possibilities would be:
- 20% (a subscriber with a purchase within the past 4 months)
- 15% (a subscriber with a purchase between 4 months and a year ago)
- 10% (for being a subscriber)
- 10% (for a purchase made within the past 4 months)
- 5% (for a purchase made between 4 months and a year ago)

Is there a way to do this all within the context of 1 query?

Ron

The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info  

Re: [PHP-DB] SELECT WHERE length of content question

[Daniel Brown]

On Wed, Mar 9, 2011 at 17:49, Ron Piggott
 wrote:
>
> Is there a command in mySQL that would allow me to SELECT the rows where the 
> `fax` column is more than 11 characters long?

There is.

SELECT * FROM tblName WHERE CHAR_LENGTH(fax) >= 11;

(Presuming you meant greater than or equal to eleven, as in an
invalid US/Canadian phone number.)

-- 

Network Infrastructure Manager
http://www.php.net/

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT WHERE length of content question

[kesavan trichy rengarajan]

Have a look at this:
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_char-length

On Thu, Mar 10, 2011 at 9:49 AM, Ron Piggott  wrote:

>
> Is there a command in mySQL that would allow me to SELECT the rows where
> the `fax` column is more than 11 characters long?
>
> OR
>
> Do I need to use PHP to assess this?
>
> Ron
>
> The Verse of the Day
> “Encouragement from God’s Word”
> http://www.TheVerseOfTheDay.info
>

[PHP-DB] SELECT WHERE length of content question

[Ron Piggott]

Is there a command in mySQL that would allow me to SELECT the rows where the 
`fax` column is more than 11 characters long? 

OR 

Do I need to use PHP to assess this?

Ron

The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info 

Re: [PHP-DB] SELECT / ORDER BY

[Richard Quadling]

On 11 September 2010 07:47, Ron Piggott  wrote:
>
> I wrote the query below to determine the 10 most popular words used:
>
> SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage,
> `bible_concordance_words`.`reference` , `bible_concordance_words`.`word`
> FROM `bible_concordance_usage`
> INNER JOIN `bible_concordance_words` ON
> `bible_concordance_usage`.`bible_concordance_words_reference` =
> `bible_concordance_words`.`reference`
> GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference`
> ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC,
> `bible_concordance_usage`.`date_accessed` DESC
> LIMIT 10
>
> What I don't like about the results is that if 8 words have been used 5
> times then the remaining 2 words the query chooses are from words used 4
> times.  The results are in alphabetical order A to Z for the words used 5
> times and back to A to Z for words used 4 times.
>
> My question: is there a way to make my query above into a "sub query" and
> have a main query order the results of the sub query "ORDER BY words ASC"
> so all the words displayed are in alphabetical order?
>
> Ron
>
> Ron
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Can't you just swap the order of the first 2 columns in the ORDER BY clause?

e.g.

ORDER BY Name, Age

will list all the names alphabetically and where there are more than 1
occurrence of a name in the result set, these would be ordered by age.

versus.

ORDER BY Age, Name

will list all the babies in alphabetical order, followed by the
toddlers, children, teenagers, adults, grannies and granddads.

So, ORDER BY  `bible_concordance_words`.`word` ASC, word_usage
DESC,`bible_concordance_usage`.`date_accessed` DESC

And as you are grouping by  `bible_concordance_words`.`word`, there
really is never going to be a duplicate. So, there is no need to order
by anything else.

So,

ORDER BY  `bible_concordance_words`.`word` ASC

is all you should need.



-- 
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT / ORDER BY

[Karl DeSaulniers]

Hello,
This may help.
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Best,
Karl


On Sep 11, 2010, at 1:47 AM, Ron Piggott wrote:



I wrote the query below to determine the 10 most popular words used:

SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage,
`bible_concordance_words`.`reference` ,  
`bible_concordance_words`.`word`

FROM `bible_concordance_usage`
INNER JOIN `bible_concordance_words` ON
`bible_concordance_usage`.`bible_concordance_words_reference` =
`bible_concordance_words`.`reference`
GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference`
ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC,
`bible_concordance_usage`.`date_accessed` DESC
LIMIT 10

What I don't like about the results is that if 8 words have been  
used 5
times then the remaining 2 words the query chooses are from words  
used 4
times.  The results are in alphabetical order A to Z for the words  
used 5

times and back to A to Z for words used 4 times.

My question: is there a way to make my query above into a "sub  
query" and
have a main query order the results of the sub query "ORDER BY  
words ASC"

so all the words displayed are in alphabetical order?

Ron

Ron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT / ORDER BY

[Ron Piggott]

I wrote the query below to determine the 10 most popular words used:

SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage,
`bible_concordance_words`.`reference` , `bible_concordance_words`.`word`
FROM `bible_concordance_usage`
INNER JOIN `bible_concordance_words` ON
`bible_concordance_usage`.`bible_concordance_words_reference` =
`bible_concordance_words`.`reference`
GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference`
ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC,
`bible_concordance_usage`.`date_accessed` DESC
LIMIT 10

What I don't like about the results is that if 8 words have been used 5
times then the remaining 2 words the query chooses are from words used 4
times.  The results are in alphabetical order A to Z for the words used 5
times and back to A to Z for words used 4 times.

My question: is there a way to make my query above into a "sub query" and
have a main query order the results of the sub query "ORDER BY words ASC"
so all the words displayed are in alphabetical order?

Ron

Ron


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select the specific user data from the database

[Phpster]

I would suggest that you keep authorization separate from data access

Bastien

Sent from my iPod

On Sep 5, 2010, at 9:19, nagendra prasad  wrote:

> PS: Want to check the username from a table and the password from another 
> table.
> 
> Is it possible ??
> 
> 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select the specific user data from the database

[nagendra prasad]

PS: Want to check the username from a table and the password from another
table.

Is it possible ??

Re: [PHP-DB] Select the specific user data from the database

[Phpster]

Then each record needs to have a user filed where their is stored. Then your 
access query just adds an additional filter to check this value

Select * from data_table where user = $user

Bastien

Sent from my iPod

On Sep 5, 2010, at 7:21, nagendra prasad  wrote:

> Hi Experts,
> 
> I have a mysql database. What I want is that when a user login he can able
> to see his entries only, so that he can delete, add or edit his entries
> only. I have 2 different tables one for user details and another for actual
> entries. Please help me.
> 
> Best,
> Guru.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Select the specific user data from the database

[nagendra prasad]

Hi Experts,

I have a mysql database. What I want is that when a user login he can able
to see his entries only, so that he can delete, add or edit his entries
only. I have 2 different tables one for user details and another for actual
entries. Please help me.

Best,
Guru.

Re: [PHP-DB] SELECT with ' in search term

[Chris]

On 13/08/10 13:26, Ron Piggott wrote:

If the variable $segment has an ' in it the $query won't work because of
having 3 ' 's.

Should I be using:

$segment = mysql_real_escape_string($segment);

before querying the database?


Use it in your query. Don't use it anywhere else. Your code may use it 
after the query and cause weird stuff, ala:


$segment = 'this is my segment';
$segment = mysql_real_escape_string($segment);
$query  = ;

echo 'My segment name is ' . htmlspecialchars($segment);

So it'll become:

$query="SELECT `reference` FROM `bible_concordance_words` WHERE `word` =
'" . mysql_real_escape_string($segment) . "' LIMIT 1";



Please note:  $segment wasn't submitted through a form.


Doesn't matter.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT with ' in search term

[Ron Piggott]

If the variable $segment has an ' in it the $query won't work because of
having 3 ' 's.

Should I be using:

$segment = mysql_real_escape_string($segment);

before querying the database?

$query="SELECT `reference` FROM `bible_concordance_words` WHERE `word` =
'$segment' LIMIT 1";

Please note:  $segment wasn't submitted through a form.

Thanks.

Ron


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT LIKE with "%" and without "%"

[Ahmet Caner]

"Eleonora De Marinis" , haber iletisinde sunlari 
yazdi:49fe92d0.2060...@garr.it...
> $sql = "SELECT * FROM table WHERE ID ='$_GET[id]' AND title LIKE
> '%$_GET[word]%'";
>
>
>
>  Original Message  
> Subject: [PHP-DB] SELECT LIKE with "%'" and without "%'"
> From: Emiliano Boragina 
> To: php-db@lists.php.net
> Date: 05/03/2009 01:43 AM
>> Hello.
>>
>> I am using this:
>> $sql = "SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE
>> '%$_GET[word]%'";
>>
>> But I want exactlu ID, not one part of many possibles Ids in the DB.
>> How can I do that?
>>
>> Thanks
>>
>> + 
>> _
>>// Emiliano Boragina _
>>// Diseño & Comunicación //
>> + 
>> _
>>// emiliano.borag...@gmail.com  /
>>// 15 40 58 60 02 ///
>> + 
>> _
>>
>
>
> 



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT LIKE with "%" and without "%"

[Philip Thompson]

On May 2, 2009, at 6:43 PM, Emiliano Boragina wrote:


Hello.

I am using this:
$sql = "SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE
'%$_GET[word]%'";

But I want exactlu ID, not one part of many possibles Ids in the DB.
How can I do that?


PLEASE tell me your cleaning that input...

http://php.net/mysql_real_escape_string

And to answer your question:



Read more on MySQL's "LIKE" functionality. Google can help you.

HTH,
~Philip

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT LIKE with "%" and without "%"

[Eleonora De Marinis]

$sql = "SELECT * FROM table WHERE ID ='$_GET[id]' AND title LIKE
'%$_GET[word]%'";



 Original Message  ----
Subject: [PHP-DB] SELECT LIKE with "%'" and without "%'"
From: Emiliano Boragina 
To: php-db@lists.php.net
Date: 05/03/2009 01:43 AM

Hello.

I am using this:
$sql = "SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE
'%$_GET[word]%'";

But I want exactlu ID, not one part of many possibles Ids in the DB.
How can I do that?

Thanks

+  _
   // Emiliano Boragina _
   // Diseño & Comunicación //
+  _
   // emiliano.borag...@gmail.com  /
   // 15 40 58 60 02 ///
+  _
  

Re: [PHP-DB] SELECT LIKE with "%" and without "%"

[mrfroasty]

Emiliano Boragina wrote:
> Hello.
>
> I am using this:
> $sql = "SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE
> '%$_GET[word]%'";
>   
This doesnt work?

$sql = "SELECT * FROM table WHERE ID='some_id' AND title='some_title';



-- 
Extra details:
OSS:Gentoo Linux
profile:x86
Hardware:msi geforce 8600GT asus p5k-se
location:/home/muhsin
language(s):C/C++,VB,VHDL,bash,PHP,SQL,HTML,CSS
Typo:40WPM
url:http://mambo-tech.net
url:http://blog.mambo-tech.net


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT LIKE with "%" and without "%"

[Emiliano Boragina]

Hello.

I am using this:
$sql = "SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE
'%$_GET[word]%'";

But I want exactlu ID, not one part of many possibles Ids in the DB.
How can I do that?

Thanks

+      _
   // Emiliano Boragina _
   // Diseño & Comunicación //
+      _
   // emiliano.borag...@gmail.com  /
   // 15 40 58 60 02 ///
+      _

[PHP-DB] SELECT query

[Ron Piggott]

I am working on a web based Bible searching query.  So far I am able to
generate:

SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON
`bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' ,
'Jesus' ) LIMIT 0 , 10

Is an "IN" the correct syntax to use?

I am trying to take what the user types in (variable is $keyword_search)
and allow a search where if the same 2 or 3 words are in the verse of
the Bible that verse would be a match, but not necessarily be beside
each other.

$keyword_search_string  = str_replace(" ", "' , '", $keyword_search);
$query .= "IN ( '" . $keyword_search_string . "' ) ";

How do I do this correctly?

Ron


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT FOR UPDATE

[Philip Thompson]

Hi all.

Which is more efficient in order to use SELECT FOR UPDATE?

%> SELECT * FROM `table` WHERE (`table_id` = 32) LIMIT 1 FOR UPDATE;
%> UPDATE `table` SET `field_name` = 'Pizza' WHERE (`table_id` = 32)  
LIMIT 1;


OR

%> SELECT `field_name` FROM `table` WHERE (`table_id` = 32) LIMIT 1  
FOR UPDATE;
%> UPDATE `table` SET `field_name` = 'Pizza' WHERE (`table_id` = 32)  
LIMIT 1;



Note: the 2nd query in each is the same. Is there any difference in  
using * and specifying the actual field you want to update? It seems  
like there would be very little difference because this is causing a  
row-lock - all the data in that row is not-updatable anyway.


I'm adding transactions and row-locking into my application. If I can  
just use the first method (because it's easier to specify '*' instead  
of every column I want to update), then I'd like to. Any thoughts are  
welcome!


Thanks in advance,

~Philip


"Personally, most of my web applications do not have to factor 13.7  
billion years of space drift in to the calculations, so PHP's rand  
function has been great for me..." ~S. Johnson


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select query with Forein key Relation

[Evert Lammerts]

It'd be consistent to use uniform naming conventions for your columns. 
E.g., an ID column is called 'id' in every table, and if you use two 
words in a column name, to separate them either by an underscore or by a 
capital letter for the second word.


In PHP a dot is an append operator - to reference a member of a certain 
namespace you use '->'. In this case you don't need it though. To get a 
column 'vname' from the table 'venue' you use:


while ($row = mysql_fetch_assoc($result)) {
   $sub = $row["venue.vname"];
}

Remember that in this case, $sub will always only hold the value of the 
last result - or of the only result if there is only one result row.


Nasreen Laghari wrote:

Hi Evert,
 
What to you mean by this "If I were you I'd go for one of them and 
apply this to all."

also to get the value of columns do i need to do following in php
 
/while ($row = mysql_fetch_array($result))

  {/
/$sub= $row[venue].[vname];/
/}/
 
Regards
 

 
- Original Message 

From: Evert Lammerts <[EMAIL PROTECTED]>
To: Nasreen Laghari <[EMAIL PROTECTED]>
Cc: php-db@lists.php.net
Sent: Wednesday, April 23, 2008 11:48:39 AM
Subject: Re: [PHP-DB] Select query with Forein key Relation

SELECT *
FROM gig
LEFT JOIN genre ON gig.genreId = genre.genreId
LEFT JOIN venue ON gig.venueID = venue.vid
WHERE gig.gigid = $gigdetail

I'd replace the dash with [table].[columnames]. Also, you're using four
different naming conventions in your columns - gigid, genreId, venueID
and vid. If I were you I'd go for one of them and apply this to all.

Evert

Nasreen Laghari wrote:
> Hi,
> 
> I have a table which contains 2 foreign key relation columns. I'm 
trying to get all columns from main table as well as all column from 
those 2 foreign key relation tables.
> 
> The query i'm using is :
> 
> select * from gig where gig.gigid = $gigDetail LEFT JOIN genre ON 
gig.genreId=genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid 
ORDER BY gig.gigid";
> 
> is this query OK?
> 
> I know  how to get value from gig table colums but how could i get 
value of columns from venue table?
> 
> Regards
> 
> Nasreen
> 
> 
> 
>

>
>
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile. 
Try it now.

>
>
>  


> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> 





Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try 
it now. 
<http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ%20>



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select query with Forein key Relation

[Evert Lammerts]

SELECT *
FROM gig
LEFT JOIN genre ON gig.genreId = genre.genreId
LEFT JOIN venue ON gig.venueID = venue.vid
WHERE gig.gigid = $gigdetail

I'd replace the dash with [table].[columnames]. Also, you're using four 
different naming conventions in your columns - gigid, genreId, venueID 
and vid. If I were you I'd go for one of them and apply this to all.


Evert

Nasreen Laghari wrote:

Hi,
 
I have a table which contains 2 foreign key relation columns. I'm trying to get all columns from main table as well as all column from those 2 foreign key relation tables. 
 
The query i'm using is :
 
select * from gig where gig.gigid = $gigDetail LEFT JOIN genre ON gig.genreId=genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid ORDER BY gig.gigid"; 
 
is this query OK? 
 
I know  how to get value from gig table colums but how could i get value of columns from venue table?
 
Regards
 
Nasreen
 
 
 




Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
  



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Select query with Forein key Relation

[Nasreen Laghari]

Hi,
 
I have a table which contains 2 foreign key relation columns. I'm trying to get 
all columns from main table as well as all column from those 2 foreign key 
relation tables. 
 
The query i'm using is :
 
select * from gig where gig.gigid = $gigDetail LEFT JOIN genre ON 
gig.genreId=genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid ORDER BY 
gig.gigid"; 
 
is this query OK? 
 
I know  how to get value from gig table colums but how could i get value of 
columns from venue table?
 
Regards
 
Nasreen
 
 
 



Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Re: [PHP-DB] SELECT query from two tables

[Ron Piggott]

Thanks

On Mon, 2008-03-10 at 12:56 +1300, Bruce Cowin wrote:
> I think what you mean to do is use IN().  And I would suggest table aliases.  
> So it could look like this:
> 
> SELECT * FROM ministry_directory md INNER JOIN
> ministry_directory_listing_categories mdlc ON md.entry = 
> mdlc.ministry_directory_entry 
> WHERE md.listing_type = 2 
> AND mdlc.ministry_directory_category_reference IN (10, 11) 
> ORDER BY ministry_directory.name ASC
> 
> 
> 
> Regards,
> 
> Bruce
> 
> >>> Ron Piggott <[EMAIL PROTECTED]> 10/03/2008 10:33:13 a.m. >>>
> I am wondering what is wrong with this syntax?  
> 
> SELECT * FROM ministry_directory INNER JOIN
> ministry_directory_listing_categories ON ministry_directory.entry =
> ministry_directory_listing_categories.ministry_directory_entry WHERE
> ministry_directory.listing_type = 2 AND
> ministry_directory_listing_categories.ministry_directory_category_reference = 
> 10 AND 
> ministry_directory_listing_categories.ministry_directory_category_reference = 
> 11 ORDER BY ministry_directory.name ASC
> 
> It produces 0 results.  
> 
> In reality there is presently 1 record that should be found that has
> listing_type = 2 and ministry_directory_category_reference 10 and 11 in
> the ministry_directory_listing_categories table
> 
> The table "ministry_directory" has the main contact information.  entry
> is auto_increment; listing_type is an INT(1) column
> 
> The table ministry_directory_listing_categories has 3 columns:
> reference which is auto_increment populated;
> ministry_directory_entry which is the common field between both tables,
> showing what the record belongs to &
> ministry_directory_category_reference which is the reference number to
> how the directory listing was inputted / categorized.  (IE If the person
> who completed the form select 2 of the 10 possible categories 2 records
> were created.)  
> 
> Is there a different way to word my query so I will be able to retrieve
> the record with two rows in table ministry_directory_listing_categories
> and 1 row in ministry_directory ?
> 
> Thanks for the help guys.
> 
> Ron
> 
> 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query from two tables

[Bruce Cowin]

I think what you mean to do is use IN().  And I would suggest table aliases.  
So it could look like this:

SELECT * FROM ministry_directory md INNER JOIN
ministry_directory_listing_categories mdlc ON md.entry = 
mdlc.ministry_directory_entry 
WHERE md.listing_type = 2 
AND mdlc.ministry_directory_category_reference IN (10, 11) 
ORDER BY ministry_directory.name ASC



Regards,

Bruce

>>> Ron Piggott <[EMAIL PROTECTED]> 10/03/2008 10:33:13 a.m. >>>
I am wondering what is wrong with this syntax?  

SELECT * FROM ministry_directory INNER JOIN
ministry_directory_listing_categories ON ministry_directory.entry =
ministry_directory_listing_categories.ministry_directory_entry WHERE
ministry_directory.listing_type = 2 AND
ministry_directory_listing_categories.ministry_directory_category_reference = 
10 AND 
ministry_directory_listing_categories.ministry_directory_category_reference = 
11 ORDER BY ministry_directory.name ASC

It produces 0 results.  

In reality there is presently 1 record that should be found that has
listing_type = 2 and ministry_directory_category_reference 10 and 11 in
the ministry_directory_listing_categories table

The table "ministry_directory" has the main contact information.  entry
is auto_increment; listing_type is an INT(1) column

The table ministry_directory_listing_categories has 3 columns:
reference which is auto_increment populated;
ministry_directory_entry which is the common field between both tables,
showing what the record belongs to &
ministry_directory_category_reference which is the reference number to
how the directory listing was inputted / categorized.  (IE If the person
who completed the form select 2 of the 10 possible categories 2 records
were created.)  

Is there a different way to word my query so I will be able to retrieve
the record with two rows in table ministry_directory_listing_categories
and 1 row in ministry_directory ?

Thanks for the help guys.

Ron


-- 
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] SELECT query from two tables

[Chris]

Ron Piggott wrote:
Two different rows Chris.  


That's the problem then.

Your query is saying "get records with category_reference of 10 and it 
has to have category_reference of 11 as well".


No such rows exist.

Maybe that should be an 'or' or 'in' (same thing).

... 
ministry_directory_listing_categories.ministry_directory_category_reference 
in (10,11)

...
;

so it can get both records 13 & 14.


reference ministry_directory_entry ministry_directory_category_reference
13  1   10
14  1   11




--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query from two tables

[Ron Piggott]

Two different rows Chris.  

reference ministry_directory_entry ministry_directory_category_reference
13  1   10
14  1   11

What I am trying to do is allow the user to make a more specific search.

Ron

On Mon, 2008-03-10 at 10:37 +1100, Chris wrote:
> 
> > ministry_directory_listing_categories.ministry_directory_category_reference 
> > = 10 AND
>  > 
> ministry_directory_listing_categories.ministry_directory_category_reference 
> = 11
> 
> Can a record really have a reference for two different id's like this? 
> ie can it be both '10' and '11' at the same time?
> 
> What's actually in the table for ministry_directory_listing_categories 
> for this record?
> 
> 
> For long table names, I'd also suggest using a table alias to make it 
> easier to read/write:
> 
> select * from table1 as a inner join table2 as b using(id)
> where a.field_name='1' and b.fieldname='5';
> 
> saves you having to write out 'ministry_directory_listing_categories' 
> and 'ministry_directory'.
> 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query from two tables

[Chris]

ministry_directory_listing_categories.ministry_directory_category_reference = 
10 AND
> 
ministry_directory_listing_categories.ministry_directory_category_reference 
= 11


Can a record really have a reference for two different id's like this? 
ie can it be both '10' and '11' at the same time?


What's actually in the table for ministry_directory_listing_categories 
for this record?



For long table names, I'd also suggest using a table alias to make it 
easier to read/write:


select * from table1 as a inner join table2 as b using(id)
where a.field_name='1' and b.fieldname='5';

saves you having to write out 'ministry_directory_listing_categories' 
and 'ministry_directory'.


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT query from two tables

[Ron Piggott]

I am wondering what is wrong with this syntax?  

SELECT * FROM ministry_directory INNER JOIN
ministry_directory_listing_categories ON ministry_directory.entry =
ministry_directory_listing_categories.ministry_directory_entry WHERE
ministry_directory.listing_type = 2 AND
ministry_directory_listing_categories.ministry_directory_category_reference = 
10 AND 
ministry_directory_listing_categories.ministry_directory_category_reference = 
11 ORDER BY ministry_directory.name ASC

It produces 0 results.  

In reality there is presently 1 record that should be found that has
listing_type = 2 and ministry_directory_category_reference 10 and 11 in
the ministry_directory_listing_categories table

The table "ministry_directory" has the main contact information.  entry
is auto_increment; listing_type is an INT(1) column

The table ministry_directory_listing_categories has 3 columns:
reference which is auto_increment populated;
ministry_directory_entry which is the common field between both tables,
showing what the record belongs to &
ministry_directory_category_reference which is the reference number to
how the directory listing was inputted / categorized.  (IE If the person
who completed the form select 2 of the 10 possible categories 2 records
were created.)  

Is there a different way to word my query so I will be able to retrieve
the record with two rows in table ministry_directory_listing_categories
and 1 row in ministry_directory ?

Thanks for the help guys.

Ron


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[TG]

$query = mysql_query("SELECT * FROM gig WHERE gigName='$gig_name' OR 
gig_fdate='$sdate'");

You only use the WHERE clause once then use parenthesis, AND and OR to create 
the logical conditions.

If you have access to the mysql server, maybe through phpMyAdmin or 
something, I'd highly recommend forming your SQL statements using that, 
then creating your PHP once you've perfected your SQL.

SQL statements can be very powerful and sometimes dangerous and it's much 
easier to debug the SQL when you work with it by itself and not have to 
worry about any PHP issues too.

Assuming your MySQL server is on another server, if you have a Windows 
machine you can use a program like WinSQL Lite or Navicat to connection to 
the MySQL server (if it allows remote connections).

phpMyAdmin is probably the easiest option though.

-TG

- Original Message -
From: Nasreen Laghari <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Date: Wed, 27 Feb 2008 15:44:23 -0800 (PST)
Subject: [PHP-DB] SELECT query with multiple "WHERE" Clause

> Hi All,
> 
> Thank you for increasing my knowledge about PHP/MYSQL.
> 
> I am creating a SEARCH, by only using one table. The search form  is same 
as Inserting item (search has form of all fields in table ), difference is 
SEARCH page doesnt have validation . Therefore user can enter information 
in any of field. I would like to know how to write a SELECT query which has 
multiple where clause with OR operator.
> 
> shall we write:
> 
> $query = mysql_query("SELECT * from gig WHERE Name='$name' || WHERE 
gig_fdate='$sdate'");
> 
> OR
> 
> $query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' OR WHERE 
gig_fdate='$sdate'");
> 
> OR
> 
> $query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' ||  
gig_fdate='$sdate'");
> 
> 
> Regards
> 
> Nasreen

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[Stephen Johnson]

$query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' or
gig_fdate='$sdate'");

You can not use more then one WHERE in your sql statement... And SQL accepts
OR and AND..  


--
Stephen Johnson c | eh
The Lone Coder

http://www.thelonecoder.com
continuing the struggle against bad code

http://www.fortheloveofgeeks.com
I¹m a geek and I¹m OK!
--




> From: Nasreen Laghari <[EMAIL PROTECTED]>
> Date: Wed, 27 Feb 2008 15:44:23 -0800 (PST)
> To: 
> Subject: [PHP-DB] SELECT query with multiple "WHERE" Clause
> 
> 
> 
> $query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' ||
> gig_fdate='$sdate'");

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[Stut]

On 27 Feb 2008, at 23:44, Nasreen Laghari wrote:

Thank you for increasing my knowledge about PHP/MYSQL.


The question you ask below is basic SQL syntax. Please read the MySQL  
manual before asking here - answers at this level are all in there.


http://mysql.com/doc

Oh, and once you have it working try entering

';delete * from gig;select * from gig where Name='

(including quotes) into the gig_name form field. When you get over the  
loss of all your data go read about sanitising your input: http://php.net/mysql_real_escape_string


-Stut

--
http://stut.net/



I am creating a SEARCH, by only using one table. The search form  is  
same as Inserting item (search has form of all fields in table ),  
difference is SEARCH page doesnt have validation . Therefore user  
can enter information in any of field. I would like to know how to  
write a SELECT query which has multiple where clause with OR operator.


shall we write:

$query = mysql_query("SELECT * from gig WHERE Name='$name' || WHERE  
gig_fdate='$sdate'");


OR

$query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' OR  
WHERE gig_fdate='$sdate'");


OR

$query = mysql_query("SELECT * from gig WHERE gigName='$gig_name'  
||  gig_fdate='$sdate'");



Regards

Nasreen


  


Looking for last minute shopping deals?
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[Daniel Brown]

On Wed, Feb 27, 2008 at 6:44 PM, Nasreen Laghari
<[EMAIL PROTECTED]> wrote:
>  I am creating a SEARCH, by only using one table. The search form  is same as 
> Inserting item (search has form of all fields in table ), difference is 
> SEARCH page doesnt have validation . Therefore user can enter information in 
> any of field. I would like to know how to write a SELECT query which has 
> multiple where clause with OR operator.

SELECT * FROM tableName WHERE (colA LIKE '%value%' OR colB='1');

--- more ---

SELECT fieldA,fieldR,fieldT,fieldX FROM tableName WHERE
(colA='value' OR colB LIKE 'Hello%') AND colC='Active';

-- 


Daniel P. Brown
Senior Unix Geek


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[Chris]

Greg Bowser wrote:

In MySQL, both "OR" and "||" are valid logical or operators.  You can only
have one Where clause, thus your last example is correct.


Though in postgresql and db2 (and some other dbs) "||" means 
"concatenate" so stick with using the word "OR" in this situation 
otherwise you'll run into portability issues if you ever needed to move 
to another db.


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[Greg Bowser]

In MySQL, both "OR" and "||" are valid logical or operators.  You can only
have one Where clause, thus your last example is correct.

--GREG

On Wed, Feb 27, 2008 at 6:44 PM, Nasreen Laghari <[EMAIL PROTECTED]>
wrote:

> Hi All,
>
> Thank you for increasing my knowledge about PHP/MYSQL.
>
> I am creating a SEARCH, by only using one table. The search form  is same
> as Inserting item (search has form of all fields in table ), difference is
> SEARCH page doesnt have validation . Therefore user can enter information in
> any of field. I would like to know how to write a SELECT query which has
> multiple where clause with OR operator.
>
> shall we write:
>
> $query = mysql_query("SELECT * from gig WHERE Name='$name' || WHERE
> gig_fdate='$sdate'");
>
> OR
>
> $query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' OR WHERE
> gig_fdate='$sdate'");
>
> OR
>
> $query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' ||
>  gig_fdate='$sdate'");
>
>
> Regards
>
> Nasreen
>
>
>
>  
> 
> Looking for last minute shopping deals?
> Find them fast with Yahoo! Search.
> http://tools.search.yahoo.com/newsearch/category.php?category=shopping
>

Re: [PHP-DB] SELECT query with multiple "WHERE" Clause

[Chris]

$query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' OR WHERE 
gig_fdate='$sdate'");


This one.

I'd suggest you get a book to help you with the basics, something like 
this should do (first hit in amazon, haven't actually read this 
particular book):


http://www.amazon.com/Learning-MySQL-Seyed-Saied-Tahaghoghi/dp/0596008643/

There's lots of stuff to learn in sql.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT query with multiple "WHERE" Clause

[Nasreen Laghari]

Hi All,

Thank you for increasing my knowledge about PHP/MYSQL.

I am creating a SEARCH, by only using one table. The search form  is same as 
Inserting item (search has form of all fields in table ), difference is SEARCH 
page doesnt have validation . Therefore user can enter information in any of 
field. I would like to know how to write a SELECT query which has multiple 
where clause with OR operator.

shall we write:

$query = mysql_query("SELECT * from gig WHERE Name='$name' || WHERE 
gig_fdate='$sdate'");

OR

$query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' OR WHERE 
gig_fdate='$sdate'");

OR

$query = mysql_query("SELECT * from gig WHERE gigName='$gig_name' ||  
gig_fdate='$sdate'");


Regards

Nasreen


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

Re: [PHP-DB] Select...

[OKi98]

 Original Message  
Subject: [PHP-DB] Select...
From: Miguel Guirao <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Date: 15.1.2008 4:44

Hello List,

I'm having kind of trouble to get done this: Select data from a table,
except those data already in a second table. Actually,  if there is a rowid
in table2, I wont get it from table1, rowid is the key that relates both
tables.

I just can't express this with a SQL statement!! idequipomed is the key that
relates both tables!!
So, if idequipomed is already in Table2, I shouldn't get it from Table1.
Any suggestions?

  
Many ways to do this. Choose the solution that gives you the best 
performance.


Solution 1:
SELECT t2.idequipomed
FROM table2 t2
WHERE NOT EXISTS (
   SELECT 1 FROM table1 WHERE table1.idequipomed = t2.idequipomed
   )

Solution 2:
SELECT idequipomed
FROM table2
WHERE idequipomed NOT IN (SELECT idequipomed FROM table1)

Solution 3:
SELECT table2.idequipomed
FROM table2
LEFT JOIN table1 ON table1.idequipomed = table2.idequipomed
WHERE table1.idequipomed IS NULL

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select...

[Niel Archer]

Hi

First off, please create your own thread, do not reply to someone else's
and change the subject.

> I'm having kind of trouble to get done this: Select data from a table,
> except those data already in a second table. Actually,  if there is a rowid
> in table2, I wont get it from table1, rowid is the key that relates both
> tables.
> 
> I just can't express this with a SQL statement!! idequipomed is the key that
> relates both tables!!
> So, if idequipomed is already in Table2, I shouldn't get it from Table1.
> Any suggestions?

You need to do a join between the two tables using the common column to
make the connection. This  should get you started

SELECT * FROM Table1 RIGHT JOIN Table2 USING (idequipomed) WHERE 
Table1.idequipomed
IS NULL


--
Niel Archer

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Select...

[Miguel Guirao]

Hello List,

I'm having kind of trouble to get done this: Select data from a table,
except those data already in a second table. Actually,  if there is a rowid
in table2, I wont get it from table1, rowid is the key that relates both
tables.

I just can't express this with a SQL statement!! idequipomed is the key that
relates both tables!!
So, if idequipomed is already in Table2, I shouldn't get it from Table1.
Any suggestions?

Table1
FieldType NullKey Default Extra
Privileges
---  ---  --  --  --  --
  ---
idequipomed  tinyint(3) unsigned  PRI (NULL)  auto_increment
select,insert,update,references
idcattinyint(3)   0
select,insert,update,references
af   varchar(10)
select,insert,update,references
ns   varchar(10)
select,insert,update,references
deptovarchar(40)  YES (NULL)
select,insert,update,references
zona char(3)
select,insert,update,references
reqcal   char(1)  YES 1
select,insert,update,references
tipocal  char(1)
select,insert,update,references
estado   char(2)
select,insert,update,references
alta date -00-00
select,insert,update,references
ubicacionvarchar(15)  0
select,insert,update,references

Table2
FieldType NullKey Default  Extra
Privileges
---  ---  --  --  ---  --  -
--
calidtinyint(4) unsigned  PRI (NULL)   auto_increment
select,insert,update,references
idequipomed  tinyint(4) unsigned  0
select,insert,update,references
faseid   tinyint(4) unsigned  0
select,insert,update,references
ultimacaldate YES (NULL)
select,insert,update,references
proxcal  date YES (NULL)
select,insert,update,references
idcattinyint(4) unsigned  0
select,insert,update,references


-Original Message-
From: Daniel Brown [mailto:[EMAIL PROTECTED]
Sent: Lunes, 14 de Enero de 2008 01:55 p.m.
To: Miguel Guirao
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] md5() function


On Jan 14, 2008 2:26 PM, Miguel Guirao <[EMAIL PROTECTED]>
wrote:
> Hi!!
>
> I'm using the md5() function to encrypt a password and store it into a
> database. Now I want to retrieve that MD5 password and convert it into
it's
> human readable condition.
> Is there a function opposite to md5()??

Negative.  Once it's hashed with an MD5, SHA1, or similar
encryption method, it's (as of now) impossible to reverse.  You could
create a table with a column of unencrypted phrases, characters, and
combinations, with a second column containing the correlating hashed
string, but that's about it.

--


Daniel P. Brown
Senior Unix Geek and #1 Rated "Year's Coolest Guy" By Self Since
Nineteen-Seventy-[mumble].

--
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] ? "SELECT TABLE" Command

[Mike W.]

Chris wrote (in news:[EMAIL PROTECTED]):

> > It is an SQL query (probably MySQL, but perhaps SQLite or possibly even
PGSQL or mSQL):
>
> The line after $query should tell you what uses it ;)

Sorry, I meant the book may have been about any of those; I was skimming through
a bunch of SQL books at that time and don’t know which one I got it from.

> > $query="SELECT TABLE $tablename;";
> > if (mysql_query($query, $link)) {
> >  echo($indent."The table, '$tablename', was successfully opened.\n"); }
> >
> > To make things even stranger, it works fine in the original program that I
> > put it in (although what, if anything, it does is beyond me), but fails
> > when I try it in another program (yes, I took care of $tablename).
>
> What's the exact query that's run? Maybe $tablename contains more than just a
table name.

Nope, it’s just

SELECT TABLE lyrics;

> Different mysql version? Maybe it was in an older version of mysql but they
removed it.
>
> It doesn't work in mysql5 or mysql4.0, maybe it does in an older version
though.
>
> Looks like you're trying to check that the mysql user you connected as has
access to that table.

It really feels like a command I may have used in the SQLite analyzer or
something.  However, I’m sure I copied it from an example script in a book.  I’
ve put holds on all the books on PHP and (My)SQL at the library and will check
them all.

-- 
Mike W.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] ? "SELECT TABLE" Command

[Chris]

Please fix your reply-to address.


It
is an SQL query (probably MySQL, but perhaps SQLite or possibly even PGSQL or
mSQL):


The line after $query should tell you what uses it ;)


$query="SELECT TABLE $tablename;";
if (mysql_query($query, $link)) {
 echo($indent."The table, '$tablename', was successfully opened.\n");
}

To make things even stranger, it works fine in the original program that I put
it in (although what, if anything, it does is beyond me), but fails when I try
it in another program (yes, I took care of $tablename).


What's the exact query that's run? Maybe $tablename contains more than 
just a table name.


Different mysql version? Maybe it was in an older version of mysql but 
they removed it.


It doesn't work in mysql5 or mysql4.0, maybe it does in an older version 
though.



Looks like you're trying to check that the mysql user you connected as 
has access to that table.


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] ? "SELECT TABLE" Command

[Alec S.]

Hi,

I've got a PHP program I wrote a year or so ago with the oddest line of code.  I
know I didn't come up with it myself; I must have adapted it from a book or
something, but I have checked all the books it could have come from and could
not find it in any of them, nor can I find any such command anywhere else.  It
is an SQL query (probably MySQL, but perhaps SQLite or possibly even PGSQL or
mSQL):

$query="SELECT TABLE $tablename;";
if (mysql_query($query, $link)) {
 echo($indent."The table, '$tablename', was successfully opened.\n");
}

To make things even stranger, it works fine in the original program that I put
it in (although what, if anything, it does is beyond me), but fails when I try
it in another program (yes, I took care of $tablename).


Does anyone have any idea where this bizarre piece of code came from, what it
does, if it's valid, or why it works even though such a command does not even
seem to exist?  This is has had me baffled for several months now.


Thanks.

-- 
Mike W.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SELECT string

[Dwight Altman]

Businesses
--
id
businessName
isChristianBookstore
isGift
isHomeDecor
isSkinCareAndCosmetics
isThriftStore


CREATE TABLE `Businesses` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`businessName` VARCHAR( 64 ) NOT NULL ,
`isChristianBookstore` TINYINT( 1 ) NOT NULL ,
`isGift` TINYINT( 1 ) NOT NULL ,
`isHomeDecor` TINYINT( 1 ) NOT NULL ,
`isSkinCareAndCosmetics` TINYINT( 1 ) NOT NULL ,
`isThriftStore` TINYINT( 1 ) NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM ;


 'isChristianBookstore',
'category_42' => 'isGift',
'category_44' => 'isHomeDecor',
'category_43' => 'isSkinCareAndCosmetics',
'category_17' => 'isThriftStore'
);
$sql = "SELECT `id`, `businessName` FROM `Businesses` WHERE ";
foreach($_POST as $formField => $formValue){
if(substr($formField, 0, 9) == "category_" && substr($formValue, -1)
== "a"){//Handle any Not Selected
$sql .= "`" . $DbFieldByFormFieldMap[$formField] . "` = '0'
AND ";
}
if(substr($formField, 0, 9) == "category_" && substr($formValue, -1)
== "b"){//Handle any Must Include
$sql .= "`" . $DbFieldByFormFieldMap[$formField] . "` = '1'
AND ";
}
}
if(substr($sql, -4) == "AND "){
$sql = substr($sql, 0, -4);
}else{
$sql .= "1";
}
echo "SQL:$sql";
?>

It was getting a tad complicated with the "Could Include" using "OR" and
testing for the end of the existing $sql string, but the "Could Include"'s
just need to be omitted from the SQL query altogether.

Regards,
Dwight

God Bless!

> -Original Message-
> From: Ron Piggott [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 23, 2007 11:31 PM
> To: PHP DB
> Subject: [PHP-DB] SELECT string
> 
> 
> I am looking for help to write a SELECT syntax to help me process a
> directory searching query tool I am developing.
> 
> If you start at
> http://www.actsministrieschristianevangelism.org/ministrydirectory/ and
> under 'Step 1:' click Business a form is displayed.
> 
> My question is how would you generate the SELECT syntax for the search
> results "Could Include" a given category and "Must Include" a given
> category based on what the user has inputted through this form?
> 
> Ron

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT string

[bedul]

cmiiw.. since i don't the visual what u said bellow
- Original Message -
From: "Ron Piggott" <[EMAIL PROTECTED]>
To: "PHP DB" 
Sent: Tuesday, April 24, 2007 11:31 AM
Subject: [PHP-DB] SELECT string


>
> I am looking for help to write a SELECT syntax to help me process a
> directory searching query tool I am developing.
you have a dir like this??
root
-include
-main
-body
--admin
--user

u want to search a file inside the dir?
why don't you create a function that read inside the dir and return query
for insert as database
1. read all file inside
2. create an insert query
3. refresh the query (repair the table?)

and then.. u can use select but target it to the database not the directory

>
> If you start at
> http://www.actsministrieschristianevangelism.org/ministrydirectory/ and
> under 'Step 1:' click Business a form is displayed.
>
> My question is how would you generate the SELECT syntax for the search
> results "Could Include" a given category and "Must Include" a given
must include?? require u mean?

> category based on what the user has inputted through this form?
>
> Ron
>
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT string

[Ron Piggott]

I am looking for help to write a SELECT syntax to help me process a
directory searching query tool I am developing.  

If you start at
http://www.actsministrieschristianevangelism.org/ministrydirectory/ and
under 'Step 1:' click Business a form is displayed.  

My question is how would you generate the SELECT syntax for the search
results "Could Include" a given category and "Must Include" a given
category based on what the user has inputted through this form?

Ron

Re: [PHP-DB] SELECT date query

[Bastien Koert]

i tend to take the approach of

$next_wed = date("Y-m-d", strtotime("next wednesday"));


Bastien


From: Niel Archer <[EMAIL PROTECTED]>
Reply-To: php-db@lists.php.net
To: php-db@lists.php.net
Subject: Re: [PHP-DB] SELECT date query
Date: Sat, 07 Oct 2006 05:49:36 +0100

Hi Ron

I've made the assumption that if today is Wednesday, you still want next
Wednesday.
Try this:

$offset = array(3,2,1,7,6,5,4);
$date = explode("-", date("Y-n-j"));
$ToDay = DayOfWeek($date[0], $date[1], $date[2]);
$NextWed = date("Y-n-j", time() + ($offset[$ToDay] * 24 * 60 * 60));

// Returns a digit in range 0-6. 0 = Sunday, 6 = Saturday
function DayOfWeek($Year, $Month, $Day)
{
$t = array(0, 3, 2, 5, 0, 3, 5, 1, 4, 6, 2, 4);
$Year -= $Month < 3;
return ($Year + ($Year / 4) - ($Year / 100) + ($Year / 400) +
$t[$Month - 1] + $Day) % 7;
}


Niel

--
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] SELECT date query

[Niel Archer]

Hi

> You can make this easier with date('w').

Doh, that'll teach me to code at 5 am.  I knew there was a better way,
but couldn't think of it, the sound of my bed calling was too
distracting.

Niel

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT date query

[Hodicska Gergely]

Hi!


You can make this easier with date('w').
 $correction = array(3, 2, 1, 7, 6, 5, 4);
 list($year, $month, $day, $dayOfWeek) = explode('|', date('Y|m|d|w'));
 echo date ("Y.m.d", mktime 
(0,0,0,$month,$day+$correction[$dayOfWeek],$year));



Regards,
Felhő


Niel Archer wrote:

Hi Ron

I've made the assumption that if today is Wednesday, you still want next
Wednesday.
Try this:

$offset = array(3,2,1,7,6,5,4);
$date = explode("-", date("Y-n-j"));
$ToDay = DayOfWeek($date[0], $date[1], $date[2]);
$NextWed = date("Y-n-j", time() + ($offset[$ToDay] * 24 * 60 * 60));

// Returns a digit in range 0-6. 0 = Sunday, 6 = Saturday
function DayOfWeek($Year, $Month, $Day)
{
$t = array(0, 3, 2, 5, 0, 3, 5, 1, 4, 6, 2, 4);
$Year -= $Month < 3;
return ($Year + ($Year / 4) - ($Year / 100) + ($Year / 400) +
$t[$Month - 1] + $Day) % 7;
}


Niel



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT date query

[Niel Archer]

Hi Ron

I've made the assumption that if today is Wednesday, you still want next
Wednesday.
Try this:

$offset = array(3,2,1,7,6,5,4);
$date = explode("-", date("Y-n-j"));
$ToDay = DayOfWeek($date[0], $date[1], $date[2]);
$NextWed = date("Y-n-j", time() + ($offset[$ToDay] * 24 * 60 * 60));

// Returns a digit in range 0-6. 0 = Sunday, 6 = Saturday
function DayOfWeek($Year, $Month, $Day)
{
$t = array(0, 3, 2, 5, 0, 3, 5, 1, 4, 6, 2, 4);
$Year -= $Month < 3;
return ($Year + ($Year / 4) - ($Year / 100) + ($Year / 400) +
$t[$Month - 1] + $Day) % 7;
}


Niel

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT date query

[Ron Piggott (PHP)]

I am wondering if someone would help me write a SELECT date query ...

Weekly mailings go out every Wednesday.  I am setting up an
administration function and table to store the mailing name, PDF to be
contained within the mailing and the date for it to be used.

The SELECT query I want to create is for the next 12 records which are
going to be used to be displayed.  The first record would be next
Wednesday (not October 11th, but calendar wise based on when the script
ran) and then the following 11 Wednesdays.

SELECT * FROM christian_discipleship WHERE created_for_date =
'$next_wednesday' ORDER BY created_for_date ASC LIMIT 12

I am not sure how to generate the value for $next_wednesday

Any ideas?

Ron

RE: [PHP-DB] Select distinct field won't return distinct value

[tg-php]

Yeah I did.. just didn't have time when I posted that message.  I was 
mis-remembering what cross-tab queries did.  They're not really what the 
original poster was looking for.

For anyone interested in crosstab queries (aka "pivot" queries or tables or 
reports), here's an example of how to do it in MySQL:

http://dev.mysql.com/tech-resources/articles/wizard/index.html

But again, not what the original poster was looking for.



-TG

= = = Original message = = =

google 'cross tab queries'...there is a php/mysql example (sorry travelling 
and don't have the link) on who to create a cross table query which is what 
you are looking for

Bastien

>From: "Blanton, Bob" <[EMAIL PROTECTED]>
>To: 
><[EMAIL PROTECTED]>,,<[EMAIL PROTECTED]>
>Subject: RE: [PHP-DB] Select distinct field won't return distinct value
>Date: Tue, 6 Jun 2006 23:44:22 -0400
>
>
>I'm just learning MySQL so don't know all the syntax.  There is a "LIST"
>function in Sybase Adaptive Server Anywhere which would do that.  Is
>there an equivalent function in MySQL?
>
>Query:
>SELECT distinct niin, list(serial_number) FROM
>fmds.maintenance_equipment
>group by niin
>order by niin
>
>Output:
>niin~~list(serial_number)
>000213909~B71-11649,B71-11657,B71-11650
>000473750~BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
>000929062~2341
>001139768~2207
>
>
>
>
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED]
>Sent: Tuesday, June 06, 2006 7:48 PM
>To: php-db@lists.php.net
>Cc: [EMAIL PROTECTED]
>Subject: Re: [PHP-DB] Select distinct field won't return distinct value
>
>Correct me if I'm wrong, but it sounds like you have something like
>this:
>
>123  Joe
>124  Joe
>125  Sue
>126  John
>127  Joe
>128  Frank
>129  Sue
>
>And you want to output something like:
>
>Joe  123, 124, 127
>Sue  125, 129
>John 126
>Frank 128
>
>But what you're getting is:
>
>Joe 123
>Joe 124
>..etc
>
>You have two ways you can solve this:
>
>1. Do two SQL queries:
>
>SELECT DISTINCT cus_name FROM customers
>
>while ($result)   // forgive the pseudo-code
>   SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
>   while ($result2) 
> echo $output;
>   
>
>
>Or..
>
>2. Collect data into an array and process 'distinctness' on output
>
>SELECT cus_name, cus_id FROM customers
>
>while ($result) 
>   $cus_arr[$cus_name][] = $cus_id;
>
>
>foreach ($cus_arr as $cus_name => $cus_idarr) 
>   echo "$cus_name as ids:" . implode(", ", $cusidarr) . "\n";
>
>
>There may be some tricky ways in SQL to get the data the way you want
>it, but ultimately it's not worth the bending over backwards for (do I
>remember right that you can do it with crosstab queries?  don't even
>know if MySQL will do those properly).   Easier just to do it with one
>of the methods above.
>
>Good luck!
>
>-TG
>
>= = = Original message = = =
>
>I want select distinct field and return value of that field, but I have
>problem which is: select distinct returns duplicate value. eg, I wan
>select
>distinct customer name and id from the customer table. one customer may
>have
>different cus_ids since cus_ids are auto increment and depend on the
>purchased items. so what I want is to select distinct customer name so
>that
>I can print customer name and customer id once.
>
>here is algorithm;
>
>select distinct cus_name, cus_id from customers order by cus_name asc
>While row is not empty do
>echo"costomer name";


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Select distinct field won't return distinct value

[Bastien Koert]

google 'cross tab queries'...there is a php/mysql example (sorry travelling 
and don't have the link) on who to create a cross table query which is what 
you are looking for


Bastien


From: "Blanton, Bob" <[EMAIL PROTECTED]>
To: 
<[EMAIL PROTECTED]>,,<[EMAIL PROTECTED]>

Subject: RE: [PHP-DB] Select distinct field won't return distinct value
Date: Tue, 6 Jun 2006 23:44:22 -0400


I'm just learning MySQL so don't know all the syntax.  There is a "LIST"
function in Sybase Adaptive Server Anywhere which would do that.  Is
there an equivalent function in MySQL?

Query:
SELECT distinct niin, list(serial_number) FROM
fmds.maintenance_equipment
group by niin
order by niin

Output:
niinlist(serial_number)
000213909   B71-11649,B71-11657,B71-11650
000473750   BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
000929062   2341
001139768   2207




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 06, 2006 7:48 PM
To: php-db@lists.php.net
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Select distinct field won't return distinct value

Correct me if I'm wrong, but it sounds like you have something like
this:

123  Joe
124  Joe
125  Sue
126  John
127  Joe
128  Frank
129  Sue

And you want to output something like:

Joe  123, 124, 127
Sue  125, 129
John 126
Frank 128

But what you're getting is:

Joe 123
Joe 124
..etc

You have two ways you can solve this:

1. Do two SQL queries:

SELECT DISTINCT cus_name FROM customers

while ($result) {  // forgive the pseudo-code
  SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
  while ($result2) {
echo $output;
  }
}

Or..

2. Collect data into an array and process 'distinctness' on output

SELECT cus_name, cus_id FROM customers

while ($result) {
  $cus_arr[$cus_name][] = $cus_id;
}

foreach ($cus_arr as $cus_name => $cus_idarr) {
  echo "$cus_name as ids:" . implode(", ", $cusidarr) . "\n";
}

There may be some tricky ways in SQL to get the data the way you want
it, but ultimately it's not worth the bending over backwards for (do I
remember right that you can do it with crosstab queries?  don't even
know if MySQL will do those properly).   Easier just to do it with one
of the methods above.

Good luck!

-TG

= = = Original message = = =

I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echo"costomer name";



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select distinct field won't return distinct value

[Martin Alterisio]

I have a friend called GROUP_CONCAT, he may know what you want but he's only
available since MySQL 4.1

2006/6/7, Blanton, Bob <[EMAIL PROTECTED]>:


It is a Sybase vendor function but I was wondering if mysql had
something comparable.  I don't see anything in the manual.  Maybe the
subquery is the only way to go.


-Original Message-
From: Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 07, 2006 8:50 AM
To: Blanton, Bob
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Select distinct field won't return distinct value

Blanton, Bob wrote:
> I'm just learning MySQL so don't know all the syntax.  There is a
"LIST"
> function in Sybase Adaptive Server Anywhere which would do that.  Is
> there an equivalent function in MySQL?
>
> Query:
> SELECT distinct niin, list(serial_number) FROM
> fmds.maintenance_equipment
> group by niin
> order by niin
>
> Output:
> niin  list(serial_number)
> 000213909 B71-11649,B71-11657,B71-11650
> 000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
> 000929062 2341
> 001139768 2207

Pretty sure that's a sybase specific function. Nothing like that exists
in mysql or postgresql.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Select distinct field won't return distinct value

[Blanton, Bob]

It is a Sybase vendor function but I was wondering if mysql had
something comparable.  I don't see anything in the manual.  Maybe the
subquery is the only way to go.


-Original Message-
From: Chris [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 8:50 AM
To: Blanton, Bob
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Select distinct field won't return distinct value

Blanton, Bob wrote:
> I'm just learning MySQL so don't know all the syntax.  There is a
"LIST"
> function in Sybase Adaptive Server Anywhere which would do that.  Is
> there an equivalent function in MySQL?
> 
> Query:
> SELECT distinct niin, list(serial_number) FROM
> fmds.maintenance_equipment
> group by niin
> order by niin
> 
> Output:
> niin  list(serial_number)
> 000213909 B71-11649,B71-11657,B71-11650
> 000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
> 000929062 2341
> 001139768 2207

Pretty sure that's a sybase specific function. Nothing like that exists 
in mysql or postgresql.

-- 
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select distinct field won't return distinct value

[Chris]

Blanton, Bob wrote:

I'm just learning MySQL so don't know all the syntax.  There is a "LIST"
function in Sybase Adaptive Server Anywhere which would do that.  Is
there an equivalent function in MySQL?

Query:
SELECT distinct niin, list(serial_number) FROM
fmds.maintenance_equipment
group by niin
order by niin

Output:
niinlist(serial_number)
000213909   B71-11649,B71-11657,B71-11650
000473750   BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
000929062   2341
001139768   2207


Pretty sure that's a sybase specific function. Nothing like that exists 
in mysql or postgresql.


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Select distinct field won't return distinct value

[Blanton, Bob]

I'm just learning MySQL so don't know all the syntax.  There is a "LIST"
function in Sybase Adaptive Server Anywhere which would do that.  Is
there an equivalent function in MySQL?

Query:
SELECT distinct niin, list(serial_number) FROM
fmds.maintenance_equipment
group by niin
order by niin

Output:
niinlist(serial_number)
000213909   B71-11649,B71-11657,B71-11650
000473750   BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
000929062   2341
001139768   2207




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 06, 2006 7:48 PM
To: php-db@lists.php.net
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Select distinct field won't return distinct value

Correct me if I'm wrong, but it sounds like you have something like
this:

123  Joe
124  Joe
125  Sue
126  John
127  Joe
128  Frank
129  Sue

And you want to output something like:

Joe  123, 124, 127
Sue  125, 129
John 126
Frank 128

But what you're getting is:

Joe 123
Joe 124
..etc

You have two ways you can solve this:

1. Do two SQL queries:

SELECT DISTINCT cus_name FROM customers

while ($result) {  // forgive the pseudo-code
  SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
  while ($result2) {
echo $output;
  }
}

Or..

2. Collect data into an array and process 'distinctness' on output

SELECT cus_name, cus_id FROM customers

while ($result) {
  $cus_arr[$cus_name][] = $cus_id;
}

foreach ($cus_arr as $cus_name => $cus_idarr) {
  echo "$cus_name as ids:" . implode(", ", $cusidarr) . "\n";
}

There may be some tricky ways in SQL to get the data the way you want
it, but ultimately it's not worth the bending over backwards for (do I
remember right that you can do it with crosstab queries?  don't even
know if MySQL will do those properly).   Easier just to do it with one
of the methods above.

Good luck!

-TG

= = = Original message = = =

I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echo"costomer name";



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
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] Select distinct field won't return distinct value

[Mohamed Yusuf]

I thank you all. problem solved using two queries as TQ mentioned.

On 6/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:


Correct me if I'm wrong, but it sounds like you have something like this:

123  Joe
124  Joe
125  Sue
126  John
127  Joe
128  Frank
129  Sue

And you want to output something like:

Joe  123, 124, 127
Sue  125, 129
John 126
Frank 128

But what you're getting is:

Joe 123
Joe 124
..etc

You have two ways you can solve this:

1. Do two SQL queries:

SELECT DISTINCT cus_name FROM customers

while ($result) {  // forgive the pseudo-code
  SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
  while ($result2) {
echo $output;
  }
}

Or..

2. Collect data into an array and process 'distinctness' on output

SELECT cus_name, cus_id FROM customers

while ($result) {
  $cus_arr[$cus_name][] = $cus_id;
}

foreach ($cus_arr as $cus_name => $cus_idarr) {
  echo "$cus_name as ids:" . implode(", ", $cusidarr) . "\n";
}

There may be some tricky ways in SQL to get the data the way you want it,
but ultimately it's not worth the bending over backwards for (do I remember
right that you can do it with crosstab queries?  don't even know if MySQL
will do those properly).   Easier just to do it with one of the methods
above.

Good luck!

-TG

= = = Original message = = =

I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echo"costomer name";



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select distinct field won't return distinct value

[tg-php]

Correct me if I'm wrong, but it sounds like you have something like this:

123  Joe
124  Joe
125  Sue
126  John
127  Joe
128  Frank
129  Sue

And you want to output something like:

Joe  123, 124, 127
Sue  125, 129
John 126
Frank 128

But what you're getting is:

Joe 123
Joe 124
..etc

You have two ways you can solve this:

1. Do two SQL queries:

SELECT DISTINCT cus_name FROM customers

while ($result) {  // forgive the pseudo-code
  SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
  while ($result2) {
echo $output;
  }
}

Or..

2. Collect data into an array and process 'distinctness' on output

SELECT cus_name, cus_id FROM customers

while ($result) {
  $cus_arr[$cus_name][] = $cus_id;
}

foreach ($cus_arr as $cus_name => $cus_idarr) {
  echo "$cus_name as ids:" . implode(", ", $cusidarr) . "\n";
}

There may be some tricky ways in SQL to get the data the way you want it, but 
ultimately it's not worth the bending over backwards for (do I remember right 
that you can do it with crosstab queries?  don't even know if MySQL will do 
those properly).   Easier just to do it with one of the methods above.

Good luck!

-TG

= = = Original message = = =

I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan select
distinct customer name and id from the customer table. one customer may have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so that
I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echo"costomer name";



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Select distinct field won't return distinct value

[Brad Bonkoski]

Perhaps you should fix your data model...

but with your current set up, try:
select cus_name, cus_id from customers group by cus_name order by 
cus_name asc

-Brad


Mohamed Yusuf wrote:


I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan 
select
distinct customer name and id from the customer table. one customer 
may have

different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so 
that

I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do{
echo"costomer name";
}



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Select distinct field won't return distinct value

[Mohamed Yusuf]

I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan select
distinct customer name and id from the customer table. one customer may have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so that
I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do{
echo"costomer name";
}

RE: [PHP-DB] SELECT

[Bastien Koert]

Convert both to unix timestamps...be much easier to wrok with both date and 
time then


bastien



From: "Ron Piggott (PHP)" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: PHP DB 
Subject: [PHP-DB] SELECT
Date: Fri, 20 Jan 2006 06:00:33 -0500

Yesterday I asked how to get the date & time 90 minutes ago and I
received several responses.  Thanks.

I don't think this select statement is working "correctly".  (Correctly
being what I am intending it to do)

I took a look at the table this morning.  One record remains that was
created 2006-01-19 at 23:55:37.  These are the values of date_created
and time_created.  The current values are approximately 2006-01-20 and
05:50:00

This is the select statement I am writing about:

SELECT * FROM `table` WHERE `date_created` <= '$date_90_minutes_ago' AND
`time_created` <= '$time_90_minutes_ago'

Intellectually I know the problem: 05:50:00 is much earlier than
23:55:37 ... thus my AND is not allowing both conditions to exist
together.

Is there a way that I may modify this SELECT statement so the present
conditions continue to exist and add a second part to the SELECT
statement that if the time is 01:30:00 or higher that records from the
previous day are selected?  This continues to allow the 90 minute time
frame for users logged into my web site ... I am not sure how you would
add an OR function to the above without messing up what presently
works :)

(I am writing a SESSION function for my web site using mySQL and a cron.
The select statement I quoted above is part of the cron.)

Ron

--
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] SELECT

[Adrian Bruce]

Possibility?

WHERE (`date_created` = '$date_90_minutes_ago' AND
`time_created` <= '$time_90_minutes_ago') or 
(`date_created` < '$date_90_minutes_ago' AND

`time_created` > '01:30:00')


Ade


Ron Piggott (PHP) wrote:


Yesterday I asked how to get the date & time 90 minutes ago and I
received several responses.  Thanks.

I don't think this select statement is working "correctly".  (Correctly
being what I am intending it to do)

I took a look at the table this morning.  One record remains that was
created 2006-01-19 at 23:55:37.  These are the values of date_created
and time_created.  The current values are approximately 2006-01-20 and
05:50:00

This is the select statement I am writing about:

SELECT * FROM `table` WHERE `date_created` <= '$date_90_minutes_ago' AND
`time_created` <= '$time_90_minutes_ago'

Intellectually I know the problem: 05:50:00 is much earlier than
23:55:37 ... thus my AND is not allowing both conditions to exist
together.

Is there a way that I may modify this SELECT statement so the present
conditions continue to exist and add a second part to the SELECT
statement that if the time is 01:30:00 or higher that records from the
previous day are selected?  This continues to allow the 90 minute time
frame for users logged into my web site ... I am not sure how you would
add an OR function to the above without messing up what presently
works :)

(I am writing a SESSION function for my web site using mySQL and a cron.
The select statement I quoted above is part of the cron.)

Ron

 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT

[Ron Piggott (PHP)]

Yesterday I asked how to get the date & time 90 minutes ago and I
received several responses.  Thanks.

I don't think this select statement is working "correctly".  (Correctly
being what I am intending it to do)

I took a look at the table this morning.  One record remains that was
created 2006-01-19 at 23:55:37.  These are the values of date_created
and time_created.  The current values are approximately 2006-01-20 and
05:50:00

This is the select statement I am writing about:

SELECT * FROM `table` WHERE `date_created` <= '$date_90_minutes_ago' AND
`time_created` <= '$time_90_minutes_ago'

Intellectually I know the problem: 05:50:00 is much earlier than
23:55:37 ... thus my AND is not allowing both conditions to exist
together.

Is there a way that I may modify this SELECT statement so the present
conditions continue to exist and add a second part to the SELECT
statement that if the time is 01:30:00 or higher that records from the
previous day are selected?  This continues to allow the 90 minute time
frame for users logged into my web site ... I am not sure how you would
add an OR function to the above without messing up what presently
works :)

(I am writing a SESSION function for my web site using mySQL and a cron.
The select statement I quoted above is part of the cron.)

Ron

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Select found_rows problem...

[Yemi Obembe]

Using the select calc_found rows code (i.e select sql_calc_found_rows *
from table where ... limit 0, 10) to get the found fesult irrespective of
the limit clause...then using $count = mysql_query("select found_rows()");
with the hope of getting the returened result but got this instead: Resource
id #7.
what could be wrong?

--
http://ngBot.com
Nigeria's #1 website directory.

Re: [PHP-DB] SELECT

[Julien Bonastre]

Have you considered the GROUP BY statement of SQL??


For example if this is a table's data:

msg_iduservarnamevalue
2   1yadahello
3   1yadahows it going?
4   2yadaits me! Zonk!
6   1turmoilanyone here seen that movie 
before?

8   2turmoiloh yeah, years back!
9   1yadaand that means??


you can:
$request_varname="yada";
SELECT * FROM the_table WHERE varname = "$request_varname" GROUP BY user 
HAVING max(msg_id)



If I can recreate my "in brain" SQL engine correctly this sql should work..

I'm not sure of exactly what your setup is like relating to storage of the 
data, and I'm not certain I understand what you are trying to retrieve with 
your query either, but I hope I'm heading down the right track..



I'm sure either way that its the GROUP BY predicate you are after..


enjoy!~


---oOo--- Allowing users to execute CGI scripts in any directory should only 
be considered if: ... a.. You have no users, and nobody ever visits your 
server. ... Extracted Quote: Security Tips - Apache HTTP 
Server ---oOo--- --oOo---oOo-- Julien Bonastre 
[The_RadiX] The-Spectrum Network CEO ABN: 64 235 749 494 
[EMAIL PROTECTED] 
www.the-spectrum.org --oOo---oOo-- 
- Original Message - 
From: "Ron Piggott (PHP)" <[EMAIL PROTECTED]>

To: "PHP DB" 
Sent: Sunday, December 11, 2005 11:04 AM
Subject: [PHP-DB] SELECT



I am trying to put together a SELECT syntax.  I am querying a response
database and I only want to obtain each user's name once even if they
have given more than 1 response.

$query="SELECT * FROM  conversation_table WHERE conversation_reference =
$conversation_currently_displayed";

$response_created_by = mysql_result($result,$i,"response_created_by");

My ideal is that if users 1, 2, 4 & 5 are in dialogue with each other
the above SELECT $query will only give the results of their identity
once with the mysql_request() function

Thanks for your help.

Ron

--
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] SELECT

[Julien Bonastre]

Yes, DISTINCT will do the same trick, I wasn't sure though as in the context 
he placed it I reconsidered DISTINCT and thought of GROUP BY because where 
there may be multiple entries/rows relating to a shared field they would 
obviously be seperated by perhaps a unique primary key, timestamp, id of 
some form, you know what i mean? And most likely obviously this field would 
be a unique field to the table, or at least to that group of rows.. And 
again, likely it would be time/date controlled, as in a incremental id 
number, or timestamp etc..



So in that case he may want the latest row??


Again as I've said in previous email, I'm not really sure what he was 
wanting to do with the query, did he want a particular row? or any row..



Anyway, thats my 5 cents...





---oOo--- Allowing users to execute CGI scripts in any directory should only 
be considered if: ... a.. You have no users, and nobody ever visits your 
server. ... Extracted Quote: Security Tips - Apache HTTP 
Server ---oOo--- --oOo---oOo-- Julien Bonastre 
[The_RadiX] The-Spectrum Network CEO ABN: 64 235 749 494 
[EMAIL PROTECTED] 
www.the-spectrum.org --oOo---oOo-- 
- Original Message - 
From: "Philip Hallstrom" <[EMAIL PROTECTED]>

To: "Ron Piggott (PHP)" <[EMAIL PROTECTED]>
Cc: "PHP DB" 
Sent: Sunday, December 11, 2005 11:20 AM
Subject: Re: [PHP-DB] SELECT



I am trying to put together a SELECT syntax.  I am querying a response
database and I only want to obtain each user's name once even if they
have given more than 1 response.

$query="SELECT * FROM  conversation_table WHERE conversation_reference =
$conversation_currently_displayed";

$response_created_by = mysql_result($result,$i,"response_created_by");

My ideal is that if users 1, 2, 4 & 5 are in dialogue with each other
the above SELECT $query will only give the results of their identity
once with the mysql_request() function


SELECT DISTINCT(user_id) FROM conversation_table WHERE 
conversation_reference = $conversation_currently_displayed


should do it.

-philip

--
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] SELECT

[Philip Hallstrom]

I am trying to put together a SELECT syntax.  I am querying a response
database and I only want to obtain each user's name once even if they
have given more than 1 response.

$query="SELECT * FROM  conversation_table WHERE conversation_reference =
$conversation_currently_displayed";

$response_created_by = mysql_result($result,$i,"response_created_by");

My ideal is that if users 1, 2, 4 & 5 are in dialogue with each other
the above SELECT $query will only give the results of their identity
once with the mysql_request() function


SELECT DISTINCT(user_id) FROM conversation_table WHERE 
conversation_reference = $conversation_currently_displayed


should do it.

-philip

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SELECT

[Ron Piggott (PHP)]

I am trying to put together a SELECT syntax.  I am querying a response
database and I only want to obtain each user's name once even if they
have given more than 1 response.  

$query="SELECT * FROM  conversation_table WHERE conversation_reference =
$conversation_currently_displayed";

$response_created_by = mysql_result($result,$i,"response_created_by");

My ideal is that if users 1, 2, 4 & 5 are in dialogue with each other
the above SELECT $query will only give the results of their identity
once with the mysql_request() function

Thanks for your help.

Ron

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SELECT & html

[Amol Hatwar]

On Sun, 2005-12-04 at 13:38 -0500, Ron Piggott (PHP) wrote:
> I have two questions.  
> 
> I would like to display the contents of my table with the first row
> being grey in background and the next row being white and the third row
> being grey, fourth being white, etc.  I am not sure how to do this.

Cool. You must use CSS for this. Your PHP must dish out  tags with
alternating CSS classes... this allows you to change colors later
without editing code. This done, now for dishing out  tag with
alternating classes you can use a function built along the following
lines:

function showRow() {
static $row = 1;
print(" Your HTML Row goes here. ");

if (2 == $row) {
$row--;
}
else {
$row++;
}
}

> 
> Secondly I only want the first 20 records to be displayed at a time and
> then I want to create a "NEXT" link for the next 20 records (21-40) ...
> any idea how you would use the SELECT command to do this?

This should be plain simple... Try reading more about SELECT. You need
to use the LIMIT clause with SELECT.

Have fun,

ah

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SELECT & html

[Robbert van Andel]

There's a couple of ways to do this.  For the row color you would set that
as you looped through the returned rows.  Using the pear db class:

$count = 0;
echo "\n";
while($db->fetchInto($data)) {
$count++;
if($count % 2 == 0) {
$bgColor = "background-color:#dcdcdc";
}
else {
$bgColor = "background-color:transparent";
}
echo "\n";
foreach($data as value) {
echo "" . htmlentities($value) . "\n";
}
echo "\n";
}

As for the limiting of rows, you would use the limit statement (assuming
you're using a DBMS that supports it).

Hopefully this helps,
Robbert van Andel

-Original Message-
From: Ron Piggott (PHP) [mailto:[EMAIL PROTECTED] 
Sent: Sunday, December 04, 2005 10:39 AM
To: PHP DB
Subject: [PHP-DB] SELECT & html 

I have two questions.  

I would like to display the contents of my table with the first row
being grey in background and the next row being white and the third row
being grey, fourth being white, etc.  I am not sure how to do this.

Secondly I only want the first 20 records to be displayed at a time and
then I want to create a "NEXT" link for the next 20 records (21-40) ...
any idea how you would use the SELECT command to do this?

Ron

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

[PHP-DB] SELECT & html

[Ron Piggott (PHP)]

I have two questions.  

I would like to display the contents of my table with the first row
being grey in background and the next row being white and the third row
being grey, fourth being white, etc.  I am not sure how to do this.

Secondly I only want the first 20 records to be displayed at a time and
then I want to create a "NEXT" link for the next 20 records (21-40) ...
any idea how you would use the SELECT command to do this?

Ron

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php