Re: [PHP-DB] Re: Maximum field length with PHP 4.1.2/MSSQL v7 ?
Hi, now I rewrote my PHP-DB-Module using COM and ADO (OLEDB), it works well, the 255 Bytes limitation doesen't exist with this method, but uff it takes a lot of memory, after openening the db-connection, the php-process increases his memory usage with about 4 MB, this seems to be a problem of COM (big overhead). But on the oder hand, if microsoft propagates this as the only method to access MS SQL, then its a problem of those customers who uses microsoft products. bye, Michael Bretterklieber wrote: Hi, I ran into the same problem, but the problem I have is that I can't switch to TEXT Datatype because MSSQL doesen't support SELECT DISTINCT or GROUP BY on them. The Workaround: SELECT CAST (fieldxyz AS TEXT) FROM tabelle abc is not useful because we build db-independent apps. I tried to recompile the MSSQL-Extension with MSSQL-Server 2000 with Service Pack 2 without success. I also changed the define DBMAXCHAR in the file Programme/Microsoft SQL Server/80/Tools/DevTools/Include/sqlfront.h to a higher value, but this does offcourse not affect precompiled libs from Microsoft. At the moment I have no idea how to solve this? :-( ... but in the Query-Analyzer is a setting where you can define the max data-length returned by a field on the fly, so there must be some settings, or the Query Analyzer doesen't use this db-lib, bye, Chris Mackenzie wrote: Frank M. Kromann wrote: Many thanks Frank, changing from varchar to text did indeed fix the problem. On reflection, I do now remember reading about this on another mailing list, it's a pitty my memory failed me when I needed it the most :-) The tool used to build the MSSQL extension for PHP (DB Library from Microsoft) was created for SQL Server 6.x and not updated with the release of 7.x or 2000. In version 6.x the max length of char and varchar columns was 255 bytes, and that is what you are seeing when you try to select data from char, nchar, varchar or nvarchar columns defined to be longar than 255 bytes. If you use the TEXT type you will not have this limitation. - Frank Hi All, I've come across an interesting problem. I'm trying to retrieve some text data (in this case a question up to 400 chars), but each time I perform a select statement I only receive the first 255 chars. What's the go here, is this just another microsoft thing ? -- Rgds, Chris MacKenzie Windows: Where do you want to go today ? Mac OS: Where do you want to be tomorrow ? Linux: Are you coming or what ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] how to ?
how to ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] searching for a software
Iv been trying to find an open source software that would calculate the number of data retrievals (from certain fixed IPs)from MySQL database. Any idea where I could find such a script? Henri -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] looking for a script
Iv been trying to find an open source software that would calculate the number of data retrievals (from certain fixed IPs)from MySQL database. Any idea where I could find such a script? Henri -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] tables/columns with spaces
Someone, in their infinite wisdom, named some, not all of course, tables and columns with spaces, i.e. BUSINESS AREA in a SQL server database that i am working with. You can run lots of queries that return fine, but when I run a query such as select * from REQ where BUSINESS AREA = '$var', i get an error. Which, to me is completely understandable. I have tried using the [BUSINESS AREA] notation, which will work on tables named with spaces, but not field names. I want to simply rename, and make the others adapt their code to conform. But i wanted to try to solve this first. Any ideas...? Thanks, Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] tables/columns with spaces
[] is the correct notation for both tables and columns. I don't ever recommend using spaces in anything, file names, tablenames, or anything else that will bite you later. But that is the correct syntax, so there may be another problem. Maybe BUSINESS AREA is numeric? Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 6:41 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] tables/columns with spaces Someone, in their infinite wisdom, named some, not all of course, tables and columns with spaces, i.e. BUSINESS AREA in a SQL server database that i am working with. You can run lots of queries that return fine, but when I run a query such as select * from REQ where BUSINESS AREA = '$var', i get an error. Which, to me is completely understandable. I have tried using the [BUSINESS AREA] notation, which will work on tables named with spaces, but not field names. I want to simply rename, and make the others adapt their code to conform. But i wanted to try to solve this first. Any ideas...? Thanks, Jeff -- 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] tables/columns with spaces
Don't i agree! I didn't create this database. and I don't use spaces for anything. that is always good pracice, but since these folks had...here is what i found. The [ ] notation, works fine on tables, i.e. [BUSINESS AREA] but when you have a column with a space then use this notation [BUSINESS_AREA]. ODBC will allow for the (space) to _(underscore) translation. Now, if you are doing joins...make sure only to bracket the table/column that needs bracketing i.e. REQ.[BUSINESS_AREA]. and don't use the _ if it's a table, i.e. [BUSINESS AREA].[BUSINESS_AREA]. Hope this helps someone else. Jeff Ryan Jameson (USA) To: [EMAIL PROTECTED] RJameson@usa. cc: ibs.org Subject: RE: [PHP-DB] tables/columns with spaces 05/10/2002 11:46 AM [] is the correct notation for both tables and columns. I don't ever recommend using spaces in anything, file names, tablenames, or anything else that will bite you later. But that is the correct syntax, so there may be another problem. Maybe BUSINESS AREA is numeric? Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 6:41 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] tables/columns with spaces Someone, in their infinite wisdom, named some, not all of course, tables and columns with spaces, i.e. BUSINESS AREA in a SQL server database that i am working with. You can run lots of queries that return fine, but when I run a query such as select * from REQ where BUSINESS AREA = '$var', i get an error. Which, to me is completely understandable. I have tried using the [BUSINESS AREA] notation, which will work on tables named with spaces, but not field names. I want to simply rename, and make the others adapt their code to conform. But i wanted to try to solve this first. Any ideas...? Thanks, Jeff -- 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] Ordering question .....
Hi Bogdan et al...
Thanks! I've got that going now. I'm sure that I'll have more questions for
you as I work on it. In fact here's one now!
echo($myrow[1] $myrow[2]br\n);
In the line above, how can I get a tab to work? I tried \t but that did not
work. Also the \n does not work on this line. In my program I took it out.
Thanks
JIM
On Thursday 09 May 2002 18:40, Bogdan Stancescu wrote:
You could create a form using PHP's great feature of correctly
retrieving arrays from POST requests. For example:
-- page1.php --
form action=page2.php method=post
?
// [connect to db]
$sql=select id, name from stamp order by name;
$result=mysql_run_query($sql);
while ($myrow=mysql_fetch_row($result)) {
echo(input type=\hidden\ name=\ids[]\ value=\$myrow[0]\\n);
echo(input type=\text\ name=\quantities[]\\n);
echo($myrow[1]br\n);
}
?
input type=submit
/form
---
and then walk $_POST[quantities] using an incrementing index ($i) and
for every non-null value of $_POST[quanitites][$i] you echo whatever
you please from the database based on id $_POST[ids][$i].
HTH
Bogdan
Jim Hatridge wrote:
HI all..
This is my first question to this list. I'm running mysql and SuSE 7.2
with PHP 4.
My question: I have a table of stamps to sell (I'm a stamp dealer btw). I
want to make a order form with all the items in this table something like
this:
_ New Stamp #1
_ New Stamp #2
And so on thru the table. I want to be able to put a amount in _
ie _4 New Stamps #1 . Then when I click the submit button PHP will
make a new table of only the stamps with an amount in the _. Also
the number of stamps in the table might (will) change each time I use
this form, so I can't say X number of lines.
Can someone give me a clue on how to do this? Please keep it simple I'm
new to this.
Thanks!
JIM
--
Jim Hatridge
Linux User #88484
--
BayerWulf
Linux System # 129656
The Recycled Beowulf Project
Looking for throw-away or obsolete computers and parts
to recycle into a Linux super computer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Ordering question .....
/t is a tab, however tabs won't show up in HTML, you'll have to use something like:
nbsp;nbsp;nbsp;nbsp;nbsp;
Ryan
-Original Message-
From: Jim Hatridge [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 10, 2002 10:03 AM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Ordering question .
Hi Bogdan et al...
Thanks! I've got that going now. I'm sure that I'll have more questions for
you as I work on it. In fact here's one now!
echo($myrow[1] $myrow[2]br\n);
In the line above, how can I get a tab to work? I tried \t but that did not
work. Also the \n does not work on this line. In my program I took it out.
Thanks
JIM
On Thursday 09 May 2002 18:40, Bogdan Stancescu wrote:
You could create a form using PHP's great feature of correctly
retrieving arrays from POST requests. For example:
-- page1.php --
form action=page2.php method=post
?
// [connect to db]
$sql=select id, name from stamp order by name;
$result=mysql_run_query($sql);
while ($myrow=mysql_fetch_row($result)) {
echo(input type=\hidden\ name=\ids[]\ value=\$myrow[0]\\n);
echo(input type=\text\ name=\quantities[]\\n);
echo($myrow[1]br\n);
}
?
input type=submit
/form
---
and then walk $_POST[quantities] using an incrementing index ($i) and
for every non-null value of $_POST[quanitites][$i] you echo whatever
you please from the database based on id $_POST[ids][$i].
HTH
Bogdan
Jim Hatridge wrote:
HI all..
This is my first question to this list. I'm running mysql and SuSE 7.2
with PHP 4.
My question: I have a table of stamps to sell (I'm a stamp dealer btw). I
want to make a order form with all the items in this table something like
this:
_ New Stamp #1
_ New Stamp #2
And so on thru the table. I want to be able to put a amount in _
ie _4 New Stamps #1 . Then when I click the submit button PHP will
make a new table of only the stamps with an amount in the _. Also
the number of stamps in the table might (will) change each time I use
this form, so I can't say X number of lines.
Can someone give me a clue on how to do this? Please keep it simple I'm
new to this.
Thanks!
JIM
--
Jim Hatridge
Linux User #88484
--
BayerWulf
Linux System # 129656
The Recycled Beowulf Project
Looking for throw-away or obsolete computers and parts
to recycle into a Linux super computer
--
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] Ordering question .....
BTW... if you're going for an outline effect, you may want to look into
ul or ol (unordered and ordered lists)
lifirst/ li
lisecond/ li
lithird/ li
/ ul
Ryan
-Original Message-
From: Ryan Jameson (USA)
Sent: Friday, May 10, 2002 10:15 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Ordering question .
/t is a tab, however tabs won't show up in HTML, you'll have to use something like:
nbsp;nbsp;nbsp;nbsp;nbsp;
Ryan
-Original Message-
From: Jim Hatridge [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 10, 2002 10:03 AM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Ordering question .
Hi Bogdan et al...
Thanks! I've got that going now. I'm sure that I'll have more questions for
you as I work on it. In fact here's one now!
echo($myrow[1] $myrow[2]br\n);
In the line above, how can I get a tab to work? I tried \t but that did not
work. Also the \n does not work on this line. In my program I took it out.
Thanks
JIM
On Thursday 09 May 2002 18:40, Bogdan Stancescu wrote:
You could create a form using PHP's great feature of correctly
retrieving arrays from POST requests. For example:
-- page1.php --
form action=page2.php method=post
?
// [connect to db]
$sql=select id, name from stamp order by name;
$result=mysql_run_query($sql);
while ($myrow=mysql_fetch_row($result)) {
echo(input type=\hidden\ name=\ids[]\ value=\$myrow[0]\\n);
echo(input type=\text\ name=\quantities[]\\n);
echo($myrow[1]br\n);
}
?
input type=submit
/form
---
and then walk $_POST[quantities] using an incrementing index ($i) and
for every non-null value of $_POST[quanitites][$i] you echo whatever
you please from the database based on id $_POST[ids][$i].
HTH
Bogdan
Jim Hatridge wrote:
HI all..
This is my first question to this list. I'm running mysql and SuSE 7.2
with PHP 4.
My question: I have a table of stamps to sell (I'm a stamp dealer btw). I
want to make a order form with all the items in this table something like
this:
_ New Stamp #1
_ New Stamp #2
And so on thru the table. I want to be able to put a amount in _
ie _4 New Stamps #1 . Then when I click the submit button PHP will
make a new table of only the stamps with an amount in the _. Also
the number of stamps in the table might (will) change each time I use
this form, so I can't say X number of lines.
Can someone give me a clue on how to do this? Please keep it simple I'm
new to this.
Thanks!
JIM
--
Jim Hatridge
Linux User #88484
--
BayerWulf
Linux System # 129656
The Recycled Beowulf Project
Looking for throw-away or obsolete computers and parts
to recycle into a Linux super computer
--
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
[PHP-DB] stmt should return user-id only once. difficult select statement.
Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] stmt should return user-id only once. difficult select statement.
This may only be preference but you've mixed join syntaxes here, it would be easier to read if you stuck with just one. Anyway, I think what you'll want is to max the whole expression. So, keep the group by, but use max (if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0)) as ranking that should work. Ryan - SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:54 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] stmt should return user-id only once. difficult select statement. Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- 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] stmt should return user-id only once. difficult select statement.
unfortunatelly not. I did already try this. It does not make a difference. Still three results for each user do you have another idea? thanks, Andy Ryan Jameson [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... This may only be preference but you've mixed join syntaxes here, it would be easier to read if you stuck with just one. Anyway, I think what you'll want is to max the whole expression. So, keep the group by, but use max (if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0)) as ranking that should work. Ryan - SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:54 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] stmt should return user-id only once. difficult select statement. Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- 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] error loading libifgen.so
I'm trying to use PHP with Informix support. If I use Apache with dynamic modules, I get the following error when I try to load the Apache server: Cannot load /usr/local/apache/libexec/libphp4.so into server: /opt/informix/lib/esql/libifgen.so: undefined symbol: stat Apache loads if I use static modules. However, when I call the ifx_connect(), no data is returned from the PHP page. However, the following error is generated in the Apache error log: /usr/local/apache/bin/httpd: error while loading shared libraries: /opt/informix/lib/esql/libifgen.so: undefined symbol: stat They are basically the same error. Any ideas on what is wrong? Is there a way to load libifgen.a instead of libifgen.so when installing PHP? Version Information RedHat 7.2 Linux Kernel 2.4.7-10 Apache 1.3.24 PHP 4.1.2 Informix Client SDK 2.70.UC3-1 for Intel Linux 2.2.14 GLIBC2.1.3 Ron Andrews Technical Services Nebo School District 801-354-7402 ext 1406 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] stmt should return user-id only once. difficult select statement.
That's only when they have a different language_id... the group by needs to have just user_id if you want to guarantee one user_id. GROUP BY u.user_id, l.language_id -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] stmt should return user-id only once. difficult select statement. unfortunatelly not. I did already try this. It does not make a difference. Still three results for each user do you have another idea? thanks, Andy Ryan Jameson [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... This may only be preference but you've mixed join syntaxes here, it would be easier to read if you stuck with just one. Anyway, I think what you'll want is to max the whole expression. So, keep the group by, but use max (if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0)) as ranking that should work. Ryan - SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:54 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] stmt should return user-id only once. difficult select statement. Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- 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
[PHP-DB] autoincrement start with higher number
Is it possible to have an autoincrement field start with a number, so the first record would be, let's say, 1000 instead of 1? The next would be 1001 instead of 2, etc. kind regards, bill hollett -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] autoincrement start with higher number
Hello!! ALTER TABLE Table1 AUTO_INCREMENT = 1000; Dan On Friday, May 10, 2002, at 04:36 PM, [EMAIL PROTECTED] wrote: Is it possible to have an autoincrement field start with a number, so the first record would be, let's say, 1000 instead of 1? The next would be 1001 instead of 2, etc. kind regards, bill hollett -- 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] stmt should return user-id only once. difficult select statement.
I agree, you are right, but that does not take me closer to the solution. So if I leave out the language_id in the group by statement I do get only the first language_id counted. Example: language table: language_id | user_id hr20 en20 gm20 The statement would return only if I search for language hr, because it is the first it finds. If I search for en I do get nothing. Maybe I can't see what you mean. Please correct me, it sounds wired to me as well Andy Ryan Jameson schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... That's only when they have a different language_id... the group by needs to have just user_id if you want to guarantee one user_id. GROUP BY u.user_id, l.language_id -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] stmt should return user-id only once. difficult select statement. unfortunatelly not. I did already try this. It does not make a difference. Still three results for each user do you have another idea? thanks, Andy Ryan Jameson [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... This may only be preference but you've mixed join syntaxes here, it would be easier to read if you stuck with just one. Anyway, I think what you'll want is to max the whole expression. So, keep the group by, but use max (if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0)) as ranking that should work. Ryan - SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:54 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] stmt should return user-id only once. difficult select statement. Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- 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
[PHP-DB] Does mysql_connect() perform password encryption?
Hello, Gurus, Sorry for this type of question but I can't find this info neither in MySQL manual, nor in PHP docs. To my understanding, when I call mysql_connect() and provide password php interpreter does password encryption, then opens socket to remote mysqld daemon and sends him encrypted password over network. There it is compared with the same encrypted password stored in mysql.user table. Now two questions: 1. Am I right? 2. Where it is described? Thank you in advance, Alexander Tarkhov, MIPT student, Moscow -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] stmt should return user-id only once. difficult select statement.
I may be confused as to what you're looking for, however I can tell you that the group by is done after the result set is created, hence the where clause is processed. So, if you search for where u.country = 'hr' then you will only ever get 'hr' in the country field after the group by is processed. So if you mean by saying search for language hr that you put that criteria in your where clause then it means there is no row where the criteria is met. If you're hope is to be able to search the result of the first query for languages then you will have to have multiple rows per user id, one for each country. Ryan -Original Message- From: Andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 4:10 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] stmt should return user-id only once. difficult select statement. I agree, you are right, but that does not take me closer to the solution. So if I leave out the language_id in the group by statement I do get only the first language_id counted. Example: language table: language_id | user_id hr20 en20 gm20 The statement would return only if I search for language hr, because it is the first it finds. If I search for en I do get nothing. Maybe I can't see what you mean. Please correct me, it sounds wired to me as well Andy Ryan Jameson schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... That's only when they have a different language_id... the group by needs to have just user_id if you want to guarantee one user_id. GROUP BY u.user_id, l.language_id -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] stmt should return user-id only once. difficult select statement. unfortunatelly not. I did already try this. It does not make a difference. Still three results for each user do you have another idea? thanks, Andy Ryan Jameson [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... This may only be preference but you've mixed join syntaxes here, it would be easier to read if you stuck with just one. Anyway, I think what you'll want is to max the whole expression. So, keep the group by, but use max (if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0)) as ranking that should work. Ryan - SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:54 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] stmt should return user-id only once. difficult select statement. Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Where to get MySQL help
I may need to convert an Interbase program to MySQL and I have some questions about MySQL. Todd -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Where to get MySQL help
On 10 May 2002 at 17:24, Todd Cary wrote: I may need to convert an Interbase program to MySQL and I have some questions about MySQL. http://www.mysql.com/ would probably be a good place to start. -- Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Does mysql_connect() perform password encryption?
On Saturday 11 May 2002 06:25, alex wrote: Hello, Gurus, Sorry for this type of question but I can't find this info neither in MySQL manual, nor in PHP docs. To my understanding, when I call mysql_connect() and provide password php interpreter does password encryption, then opens socket to remote mysqld daemon and sends him encrypted password over network. There it is compared with the same encrypted password stored in mysql.user table. Now two questions: 1. Am I right? 2. Where it is described? In the manual (General Security Issues and the MySQL Access Privilege System) -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Darth Vader! Only you would be so bold! -- Princess Leia Organa */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
