show tables WHERE ?

Hello All,

On my rented space I have only one database. I have a few users at my
site
and any mysql tables I made for them have their initials as a prefix
i.e.
aa_phonelist, aa_addresses, aa_otherjunk
bb_phonelist, bb_addresses, bb_otherjunk
I want to make a drop down selection for the tables so that each can see
his/her tables
but ONLY theirs. What I really want is a query like 'Show tables where
prefix like aa_'
and send the results to the drop down. Obviously that won't work but I
have wracked my
small brain for quite some time now and can't seem to find something that
will work.
I have tried to use the show table results with things like in_array but I
just can't
make it work. This is on a logged-in page so I know the prefix but I
can't seem to limit
show tables at all and I can't find a way to strip out the other prefixes
from the resulting
array. Not zillions but quite a few tables and new ones all the time, so
this needs to be
dynamic. Anyone with help TIA

best regards

m

Re: show tables WHERE ?

malcolm wrote:
>
> Hello All,
>
> On my rented space I have only one database. I have a few users at my
> site
> and any mysql tables I made for them have their initials as a prefix
> i.e.
> aa_phonelist, aa_addresses, aa_otherjunk
> bb_phonelist, bb_addresses, bb_otherjunk
> I want to make a drop down selection for the tables so that each can
> see his/her tables
> but ONLY theirs. What I really want is a query like 'Show tables where
> prefix like aa_'
> and send the results to the drop down. Obviously that won't work but I
> have wracked my
> small brain for quite some time now and can't seem to find something
> that will work.
> I have tried to use the show table results with things like in_array but
> I just can't
> make it work. This is on a logged-in page so I know the prefix but I
> can't seem to limit
> show tables at all and I can't find a way to strip out the other
> prefixes from the resulting
> array. Not zillions but quite a few tables and new ones all the time, so
> this needs to be
> dynamic. Anyone with help TIA
>
> best regards
>
> m

Look at the mysql_list_tables() function, if you aren't using mysql on the
host, then it will be more tricky. If you use the function, then you will need
to filter the result when you got it to PHP, while the alternative options
could work with WHERE statements.


//Aho

Re: show tables WHERE ?

malcolm wrote:
>
> Hello All,
>
> On my rented space I have only one database. I have a few users at my
> site
> and any mysql tables I made for them have their initials as a prefix
> i.e.
> aa_phonelist, aa_addresses, aa_otherjunk
> bb_phonelist, bb_addresses, bb_otherjunk
> I want to make a drop down selection for the tables so that each can
> see his/her tables
> but ONLY theirs. What I really want is a query like 'Show tables where
> prefix like aa_'
> and send the results to the drop down. Obviously that won't work but I
> have wracked my
> small brain for quite some time now and can't seem to find something
> that will work.
> I have tried to use the show table results with things like in_array but
> I just can't
> make it work. This is on a logged-in page so I know the prefix but I
> can't seem to limit
> show tables at all and I can't find a way to strip out the other
> prefixes from the resulting
> array. Not zillions but quite a few tables and new ones all the time, so
> this needs to be
> dynamic.

Look at the mysql_list_tables() function, if you aren't using mysql on the
host, then it will be more tricky. If you use the function, then you will need
to filter the result when you got it to PHP, while the alternative options
could work with WHERE statements.

http://www.php.net/manual/en/function.mysql-list-tables.php


//Aho

Re: show tables WHERE ?

On Mon, 27 Feb 2006 17:29:51 -0500, J.O. Aho wrote:

> malcolm wrote:
>> Hello All,
>> On my rented space I have only one database. I have a few users at
>> my site
>> and any mysql tables I made for them have their initials as a prefix
>> i.e.
>> aa_phonelist, aa_addresses, aa_otherjunk
>> bb_phonelist, bb_addresses, bb_otherjunk
>> I want to make a drop down selection for the tables so that each can
>> see his/her tables
>> but ONLY theirs. What I really want is a query like 'Show tables where
>> prefix like aa_'
>> and send the results to the drop down. Obviously that won't work but
>> I have wracked my
>> small brain for quite some time now and can't seem to find something
>> that will work.
>> I have tried to use the show table results with things like in_array
>> but I just can't
>> make it work. This is on a logged-in page so I know the prefix but I
>> can't seem to limit
>> show tables at all and I can't find a way to strip out the other
>> prefixes from the resulting
>> array. Not zillions but quite a few tables and new ones all the time,
>> so this needs to be
>> dynamic.
>
> Look at the mysql_list_tables() function, if you aren't using mysql on
> the host, then it will be more tricky. If you use the function, then you
> will need to filter the result when you got it to PHP, while the
> alternative options could work with WHERE statements.
>
> http://www.php.net/manual/en/function.mysql-list-tables.php
>
>
> //Aho


Thanks Aho but I'm able to list the tables ok and do the drop down but
I can't figure how to filter the array returned by the 'show tables' query.
That function does the same but I don't see any way there to filter the
result. I'm looking at array_clean now, thanks for reply.

Re: show tables WHERE ?

malcolm wrote:
> On Mon, 27 Feb 2006 17:29:51 -0500, J.O. Aho wrote:
>
>> malcolm wrote:
>>> Hello All,
>>> On my rented space I have only one database. I have a few users
>>> at my site
>>> and any mysql tables I made for them have their initials as a
>>> prefix i.e.
>>> aa_phonelist, aa_addresses, aa_otherjunk
>>> bb_phonelist, bb_addresses, bb_otherjunk
>>> I want to make a drop down selection for the tables so that each can
>>> see his/her tables
>>> but ONLY theirs. What I really want is a query like 'Show tables
>>> where prefix like aa_'
>>> and send the results to the drop down. Obviously that won't work
>>> but I have wracked my
>>> small brain for quite some time now and can't seem to find something
>>> that will work.
>>> I have tried to use the show table results with things like in_array
>>> but I just can't
>>> make it work. This is on a logged-in page so I know the prefix but I
>>> can't seem to limit
>>> show tables at all and I can't find a way to strip out the other
>>> prefixes from the resulting
>>> array. Not zillions but quite a few tables and new ones all the time,
>>> so this needs to be
>>> dynamic.
>>
>> Look at the mysql_list_tables() function, if you aren't using mysql on
>> the host, then it will be more tricky. If you use the function, then
>> you will need to filter the result when you got it to PHP, while the
>> alternative options could work with WHERE statements.
>>
>> http://www.php.net/manual/en/function.mysql-list-tables.php
>>
>>
>> //Aho
>
>
> Thanks Aho but I'm able to list the tables ok and do the drop down but
> I can't figure how to filter the array returned by the 'show tables' query.
> That function does the same but I don't see any way there to filter the
> result. I'm looking at array_clean now, thanks for reply.

If you save the results from the "query" into an array, you can use array_filter()

http://www.php.net/manual/en/function.array-filter.php

and a function that returns true if the initials of a string is the same as
for the user, something like this may work:

//we assume that $initials contains the initials of the user

function is_user($var) {
global $initials;
if(substr($var, 0, 2)==$initials) {
return true;
}
return false;
}

//$table_array is a array with all the tables
$user_tables=array_filter($table_array, "is_user"));

//$user_tables should only contain tables with the same initials as the users


or you can do a loop where you check which tables are okey to list and at the
same time type out the options.

for($i=0;$i if(substr($table_array[$i], 0, 2)==$initials) {
echo "".$table_array[$i]."\n";
}
}



//Aho

Re: show tables WHERE ?

> On my rented space I have only one database. I have a few users at my
> site
> and any mysql tables I made for them have their initials as a prefix
> i.e.
> aa_phonelist, aa_addresses, aa_otherjunk
> bb_phonelist, bb_addresses, bb_otherjunk
> I want to make a drop down selection for the tables so that each can see
> his/her tables
> but ONLY theirs. What I really want is a query like 'Show tables where
> prefix like aa_'
> and send the results to the drop down. Obviously that won't work



SHOW TABLES LIKE "aa_%";

---
Steve

Re: show tables WHERE ?

Post removed (X-No-Archive: yes)

Re: show tables WHERE ?

malcolm wrote:
>
> What I really want is a query like 'Show tables where prefix like aa_'

Easy, really:

SHOW TABLES LIKE 'aa_%'

Cheers,
NC

Re: show tables WHERE ?

On Mon, 27 Feb 2006 19:49:28 -0500, J.O. Aho wrote:

> malcolm wrote:
>> On Mon, 27 Feb 2006 17:29:51 -0500, J.O. Aho wrote:
>>
>>> malcolm wrote:
>>>> Hello All,
>>>> On my rented space I have only one database. I have a few users
>>>> at my site
>>>> and any mysql tables I made for them have their initials as a
>>>> prefix i.e.
>>>> aa_phonelist, aa_addresses, aa_otherjunk
>>>> bb_phonelist, bb_addresses, bb_otherjunk
>>>> I want to make a drop down selection for the tables so that each can
>>>> see his/her tables
>>>> but ONLY theirs. What I really want is a query like 'Show tables
>>>> where prefix like aa_'
>>>> and send the results to the drop down. Obviously that won't work
>>>> but I have wracked my
>>>> small brain for quite some time now and can't seem to find something
>>>> that will work.
>>>> I have tried to use the show table results with things like in_array
>>>> but I just can't
>>>> make it work. This is on a logged-in page so I know the prefix but I
>>>> can't seem to limit
>>>> show tables at all and I can't find a way to strip out the other
>>>> prefixes from the resulting
>>>> array. Not zillions but quite a few tables and new ones all the time,
>>>> so this needs to be
>>>> dynamic.
>>>
>>> Look at the mysql_list_tables() function, if you aren't using mysql on
>>> the host, then it will be more tricky. If you use the function, then
>>> you will need to filter the result when you got it to PHP, while the
>>> alternative options could work with WHERE statements.
>>>
>>> http://www.php.net/manual/en/function.mysql-list-tables.php
>>>
>>>
>>> //Aho
>> Thanks Aho but I'm able to list the tables ok and do the drop down but
>> I can't figure how to filter the array returned by the 'show tables'
>> query.
>> That function does the same but I don't see any way there to filter the
>> result. I'm looking at array_clean now, thanks for reply.
>
> If you save the results from the "query" into an array, you can use
> array_filter()
>
> http://www.php.net/manual/en/function.array-filter.php
>
> and a function that returns true if the initials of a string is the same
> as for the user, something like this may work:
>
> //we assume that $initials contains the initials of the user
>
> function is_user($var) {
> global $initials;
> if(substr($var, 0, 2)==$initials) {
> return true;
> }
> return false;
> }
>
> //$table_array is a array with all the tables
> $user_tables=array_filter($table_array, "is_user"));
>
> //$user_tables should only contain tables with the same initials as the
> users
>
>
> or you can do a loop where you check which tables are okey to list and
> at the same time type out the options.
>
> for($i=0;$i > if(substr($table_array[$i], 0, 2)==$initials) {
> echo "".$table_array[$i]."\n";
> }
> }
>
>
>
> //Aho


Thank you again Aho but I can't seem to get anything out of your code.
I have tried all the things I can think of but I get no output. No errors
and if I print_r the table_array I can see the array but if I run this code
no matter the initials I use, I get no output. Still working, thanks
again.

Re: show tables WHERE ?

indeed !

I spent hours trying to make a 'WHERE'

good grief !

thank you

On Tue, 28 Feb 2006 09:01:23 -0500, Steve
wrote:

>
>> On my rented space I have only one database. I have a few users at
>> my
>> site
>> and any mysql tables I made for them have their initials as a prefix
>> i.e.
>> aa_phonelist, aa_addresses, aa_otherjunk
>> bb_phonelist, bb_addresses, bb_otherjunk
>> I want to make a drop down selection for the tables so that each can
>> see
>> his/her tables
>> but ONLY theirs. What I really want is a query like 'Show tables where
>> prefix like aa_'
>> and send the results to the drop down. Obviously that won't work
>
>
>
> SHOW TABLES LIKE "aa_%";
>
> ---
> Steve
>

Re: show tables WHERE ?

Please disregard this as I have the answer from Steve
SHOW TABLES LIKE -- I hadn't tried LIKE

On Tue, 28 Feb 2006 16:53:51 -0500, malcolm wrote:

> On Mon, 27 Feb 2006 19:49:28 -0500, J.O. Aho wrote:
>
>> malcolm wrote:
>>> On Mon, 27 Feb 2006 17:29:51 -0500, J.O. Aho wrote:
>>>
>>>> malcolm wrote:
>>>>> Hello All,
>>>>> On my rented space I have only one database. I have a few users
>>>>> at my site
>>>>> and any mysql tables I made for them have their initials as a
>>>>> prefix i.e.
>>>>> aa_phonelist, aa_addresses, aa_otherjunk
>>>>> bb_phonelist, bb_addresses, bb_otherjunk
>>>>> I want to make a drop down selection for the tables so that each
>>>>> can see his/her tables
>>>>> but ONLY theirs. What I really want is a query like 'Show tables
>>>>> where prefix like aa_'
>>>>> and send the results to the drop down. Obviously that won't work
>>>>> but I have wracked my
>>>>> small brain for quite some time now and can't seem to find something
>>>>> that will work.
>>>>> I have tried to use the show table results with things like in_array
>>>>> but I just can't
>>>>> make it work. This is on a logged-in page so I know the prefix but
>>>>> I can't seem to limit
>>>>> show tables at all and I can't find a way to strip out the other
>>>>> prefixes from the resulting
>>>>> array. Not zillions but quite a few tables and new ones all the
>>>>> time, so this needs to be
>>>>> dynamic.
>>>>
>>>> Look at the mysql_list_tables() function, if you aren't using mysql
>>>> on the host, then it will be more tricky. If you use the function,
>>>> then you will need to filter the result when you got it to PHP, while
>>>> the alternative options could work with WHERE statements.
>>>>
>>>> http://www.php.net/manual/en/function.mysql-list-tables.php
>>>>
>>>>
>>>> //Aho
>>> Thanks Aho but I'm able to list the tables ok and do the drop down
>>> but
>>> I can't figure how to filter the array returned by the 'show tables'
>>> query.
>>> That function does the same but I don't see any way there to filter the
>>> result. I'm looking at array_clean now, thanks for reply.
>>
>> If you save the results from the "query" into an array, you can use
>> array_filter()
>>
>> http://www.php.net/manual/en/function.array-filter.php
>>
>> and a function that returns true if the initials of a string is the
>> same as for the user, something like this may work:
>>
>> //we assume that $initials contains the initials of the user
>>
>> function is_user($var) {
>> global $initials;
>> if(substr($var, 0, 2)==$initials) {
>> return true;
>> }
>> return false;
>> }
>>
>> //$table_array is a array with all the tables
>> $user_tables=array_filter($table_array, "is_user"));
>>
>> //$user_tables should only contain tables with the same initials as the
>> users
>>
>>
>> or you can do a loop where you check which tables are okey to list and
>> at the same time type out the options.
>>
>> for($i=0;$i >> if(substr($table_array[$i], 0, 2)==$initials) {
>> echo "".$table_array[$i]."\n";
>> }
>> }
>>
>>
>>
>> //Aho
>
>
> Thank you again Aho but I can't seem to get anything out of your code.
> I have tried all the things I can think of but I get no output. No
> errors
> and if I print_r the table_array I can see the array but if I run this
> code
> no matter the initials I use, I get no output. Still working, thanks
> again.