[PHP-DB] Re: MySQL two tables and an uneven number of rows

[B. Aerts]

On 12/09/16 05:24, Karl DeSaulniers wrote:

Hello All,
Hoping you can help clear my head on this. I have two MySQL tables for custom 
fields data to be stored.

custom_fields   custom_fields_meta

custom_fields is the info for the actual field displayed in the html and 
custom_fields_meta is the data stored from entering a value on said field in 
the form.

Custom fields can be added and removed at will by the user and so when for 
instance, adding a field,
it currently creates an uneven number of rows in the custom_fields_meta if 
there were any entries with fields create prior to this new one.

Currently I have this code:

$SQL = "SELECT ft.*, mt.Meta_Value
FROM `CUSTOM_FIELDS` ft
LEFT JOIN `CUSTOM_FIELDS_META` mt
ON mt.Field_ID = ft.Field_ID
WHERE mt.Order_ID=%d
ORDER BY ft.Field_ID ASC";

I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN.
If I manually put in the missing rows in the meta table, left join works.
However, manually updating prior entries is not going to happen.

So my question is how do I get all the table rows in both tables even if there 
is not a row to match on the meta table?
or
How would I update the prior entries to include this new field in the meta 
table and keep things orderly?
The meta is stored per order id and so there is groups of meta data per order 
id. I would like to avoid scattered data.
Is there a way to push the index down to fit them in or is this just going to 
be too costly on server resources?

TIA,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 






Hi Karl,

I can't really follow your problem ... Any chance to post 2 dummy table 
layouts to show what you want, and what you get ?


And it isn't something you could solve with a UNION ?

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

[PHP-DB] Re: mysql and auth plugins

[Roberto Spadim]

here the php files (download only .php files):
https://github.com/rspadim/server/blob/otp_plugin/plugin/auth_otp/
here the today output using mysql_connect: (using otp_auth_example.php php file)

==
MYSQL_CONNECT:

PHP Warning:  mysql_connect(): The server requested authentication
method unknown to the client [dialog] in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_e
xample.php on line 50

Warning: mysql_connect(): The server requested authentication method
unknown to the client [dialog] in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_exampl
e.php on line 50
PHP Warning:  mysql_connect(): The server requested authentication
method umknown to the client in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_example.ph
p on line 50

Warning: mysql_connect(): The server requested authentication method
umknown to the client in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_example.php on
line 50
Error: The server requested authentication method >>>umknown<<< to the
client   (CHECK A TYPO ERROR)


==
MYSQI_CONNECT:
C:\GIT\mariadb-tmp\server\plugin\auth_otp>php otp_auth_example.php
PHP Warning:  mysqli_connect(): The server requested authentication
method unknown to the client [dialog] in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_
example.php on line 60

Warning: mysqli_connect(): The server requested authentication method
unknown to the client [dialog] in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_examp
le.php on line 60
PHP Warning:  mysqli_connect(): (HY000/2054): The server requested
authentication method umknown to the client in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_
auth_example.php on line 60

Warning: mysqli_connect(): (HY000/2054): The server requested
authentication method umknown to the client in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_
example.php on line 60
PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean
given in C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_example.php
on line 62

Warning: mysqli_error() expects parameter 1 to be mysqli, boolean
given in C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_example.php
on line 62
Error:




===
PDO:

C:\GIT\mariadb-tmp\server\plugin\auth_otp>php otp_auth_example.php
PHP Warning:  PDO::__construct(): The server requested authentication
method unknown to the client [dialog] in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_aut
h_example.php on line 68

Warning: PDO::__construct(): The server requested authentication
method unknown to the client [dialog] in
C:\GIT\mariadb-tmp\server\plugin\auth_otp\otp_auth_exa
mple.php on line 68
Error: SQLSTATE[HY000] [2054] The server requested authentication
method umknown to the client

2014-10-22 1:44 GMT-02:00 Roberto Spadim :
> Hi guys, i'm with a doubt about mysql and connect with a mariadb
> server using a dialog auth plugin (server side)
>
> i didn't found functions to execute dialog with server
>
> a full example of what i want but using heidisql can be found here:
> http://www.heidisql.com/forum.php?t=9752
>
> any help is wellcome
>
> --
> Roberto Spadim



-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle

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

Re: [PHP-DB] Re: mysql query

[Jim Giner]

On 8/23/2013 4:32 AM, Lester Caine wrote:

Karl DeSaulniers wrote:

If your on a PC I would just get Eclipse. But if you have netbeans,
you can set the syntax highlighting for the different scripts you
write in the preferences. PHP, java, javascript, etc...


But the problem tha5 has been identified will never be flagged by simple
highlighting. Debugging complex SQL queries is possibly better done
outside of the PHP pages. Not sure exactly what SQL plug-in I've got
running on Eclipse at the moment, but as Ethan identified earlier, the
SQL script ran from the command line. It was passing the variables into
it which was wrong.

I'm with him on the statement that MySQL should have returned an error.
Certainly Firebird would have done and so identifying the problem might
have been easier.

I think the reason he didn't get the error was cause of his lack of a 
connection which never allowed it to run.


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

Re: [PHP-DB] Re: mysql query

[Jim Giner]

On 8/22/2013 8:08 PM, Ethan Rosenberg wrote:


Ethan Rosenberg, PhD
/Pres/CEO/
*Hygeia Biomedical Research, Inc*
2 Cameo Ridge Road
Monsey, NY 10952
T: 845 352-3908
F: 845 352-7566
erosenb...@hygeiabiomedical.com
On 08/22/2013 06:56 PM, Jim Giner wrote:

On 8/22/2013 4:14 PM, Ethan Rosenberg wrote:

On 08/22/2013 11:54 AM, Jim Giner wrote:

On 8/22/2013 9:52 AM, Jim Giner wrote:

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC,
quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc',
$qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt
$opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan




Ethan - you are simply missing two dollar signs as pointed out. Once
you correct them, if there are any more errors you should then be
seeing
the message from mysqli_error.

And as for the advice to dump single quotes, I'd ignore it. The use of
double and single quotes is a very handy feature and makes for very
readable code.  Escaping double quotes is such a royal pia and
makes for
more trouble deciphering code later on.  The sample you provided
for us
is some of the best and most understandable code you've ever showed
us.


Also - Ethan - if you used an editor that was designed for php you
probably would have seen these missing $ signs since a good one would
highlight php syntax and the lack of the $ would have produced a
different color than you expected.


Jim -

I  used Netbeans.  All it said is "variable unused is scope", which is
a  error that I often find does not mean anything.  I am as pressurized
as you are.  Any suggestions as to an editor?

Ethan



Did you mean to say "unused IN scope"?  That would be telling you that
it is not yet defined and that could be a problem if you expect to be
already defined.

Several other posts here have listed their favorites.  Notepad ++
seems to be a favorite.  I use HTML-kit Tools as my developing
environment. Handles highlighting for php, html and js, as well as
project organization.  Also includes an ftp engine to allow me to
modify, upload and then go test my code very quickly. (I don't run php
or apache locally.)

Jim -

Thanks.

unused IN scope - correct.

There are lots of editors mentioned in this email trail.  I thank all
for the suggestions.

Netbeans, Aptana Studio, etc will all highlight code and show the errors
the code would generate in a browse. The problem here was two missing $
signs.

I'm probably wrong, but in some contexts; eg, sql query, $ signs are not
used.  I tried and added the incorrect $ sign, and Netbeans did not
complain.  If anyone knows of an editor that will able to spot this kind
of error, please inform the list.

Ethan

Wrong in one sense - all php vars must have a $ sign.  When building a 
query statemtent, if the editor doesn't tell you something (by not 
colorizing it) Sql is going to tell you when you attempt to run it. 
That's why one should ALWAYS include an error check after any operation.


BTW - this line
echo '$sql2';

isn't going to give you what you want.

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

Re: [PHP-DB] Re: mysql query

[Lester Caine]

Karl DeSaulniers wrote:

If your on a PC I would just get Eclipse. But if you have netbeans, you can set 
the syntax highlighting for the different scripts you write in the preferences. 
PHP, java, javascript, etc...


But the problem tha5 has been identified will never be flagged by simple 
highlighting. Debugging complex SQL queries is possibly better done outside of 
the PHP pages. Not sure exactly what SQL plug-in I've got running on Eclipse at 
the moment, but as Ethan identified earlier, the SQL script ran from the command 
line. It was passing the variables into it which was wrong.


I'm with him on the statement that MySQL should have returned an error. 
Certainly Firebird would have done and so identifying the problem might have 
been easier.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

Re: [PHP-DB] Re: mysql query

[Karl DeSaulniers]

If your on a PC I would just get Eclipse. But if you have netbeans, you can set 
the syntax highlighting for the different scripts you write in the preferences. 
PHP, java, javascript, etc...

Best,

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] Re: mysql query

[Lester Caine]

Ethan Rosenberg wrote:

I'm probably wrong, but in some contexts; eg, sql query, $ signs are not used.
I tried and added the incorrect $ sign, and Netbeans did not complain.  If
anyone knows of an editor that will able to spot this kind of error, please
inform the list.


You do need to take a little more care when using variables IN strings and watch 
that they are highlighted. As you say, the parsing is not actually wrong as it 
is valid 'text' and adding SQL parsers for every database is not really 
practical and probably would not fix the problem anyway? Personally I use 
Firebird, and have always built the SQL using parameters, so that the SQL is 
pure text, and values are passed in an array. This is something MySQL was a lot 
later in catching onto, but many of the simple security problems are totally 
eliminated using that approach.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

Re: [PHP-DB] Re: mysql query

[Ethan Rosenberg]

Ethan Rosenberg, PhD
/Pres/CEO/
*Hygeia Biomedical Research, Inc*
2 Cameo Ridge Road
Monsey, NY 10952
T: 845 352-3908
F: 845 352-7566
erosenb...@hygeiabiomedical.com
On 08/22/2013 06:56 PM, Jim Giner wrote:

On 8/22/2013 4:14 PM, Ethan Rosenberg wrote:

On 08/22/2013 11:54 AM, Jim Giner wrote:

On 8/22/2013 9:52 AM, Jim Giner wrote:

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, 
quant,

manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', 
$qnt,'$mnf','$itm',

odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt 
$opf

$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan




Ethan - you are simply missing two dollar signs as pointed out. Once
you correct them, if there are any more errors you should then be 
seeing

the message from mysqli_error.

And as for the advice to dump single quotes, I'd ignore it. The use of
double and single quotes is a very handy feature and makes for very
readable code.  Escaping double quotes is such a royal pia and 
makes for
more trouble deciphering code later on.  The sample you provided 
for us
is some of the best and most understandable code you've ever showed 
us.



Also - Ethan - if you used an editor that was designed for php you
probably would have seen these missing $ signs since a good one would
highlight php syntax and the lack of the $ would have produced a
different color than you expected.


Jim -

I  used Netbeans.  All it said is "variable unused is scope", which is
a  error that I often find does not mean anything.  I am as pressurized
as you are.  Any suggestions as to an editor?

Ethan


Did you mean to say "unused IN scope"?  That would be telling you that 
it is not yet defined and that could be a problem if you expect to be 
already defined.


Several other posts here have listed their favorites.  Notepad ++ 
seems to be a favorite.  I use HTML-kit Tools as my developing 
environment. Handles highlighting for php, html and js, as well as 
project organization.  Also includes an ftp engine to allow me to 
modify, upload and then go test my code very quickly. (I don't run php 
or apache locally.)

Jim -

Thanks.

unused IN scope - correct.

There are lots of editors mentioned in this email trail.  I thank all 
for the suggestions.


Netbeans, Aptana Studio, etc will all highlight code and show the errors 
the code would generate in a browse. The problem here was two missing $ 
signs.


I'm probably wrong, but in some contexts; eg, sql query, $ signs are not 
used.  I tried and added the incorrect $ sign, and Netbeans did not 
complain.  If anyone knows of an editor that will able to spot this kind 
of error, please inform the list.


Ethan


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

Re: [PHP-DB] Re: mysql query

[Jim Giner]

On 8/22/2013 4:14 PM, Ethan Rosenberg wrote:

On 08/22/2013 11:54 AM, Jim Giner wrote:

On 8/22/2013 9:52 AM, Jim Giner wrote:

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', $qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt $opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan




Ethan - you are simply missing two dollar signs as pointed out. Once
you correct them, if there are any more errors you should then be seeing
the message from mysqli_error.

And as for the advice to dump single quotes, I'd ignore it.  The use of
double and single quotes is a very handy feature and makes for very
readable code.  Escaping double quotes is such a royal pia and makes for
more trouble deciphering code later on.  The sample you provided for us
is some of the best and most understandable code you've ever showed us.


Also - Ethan - if you used an editor that was designed for php you
probably would have seen these missing $ signs since a good one would
highlight php syntax and the lack of the $ would have produced a
different color than you expected.


Jim -

I  used Netbeans.  All it said is "variable unused is scope", which is
a  error that I often find does not mean anything.  I am as pressurized
as you are.  Any suggestions as to an editor?

Ethan


Did you mean to say "unused IN scope"?  That would be telling you that 
it is not yet defined and that could be a problem if you expect to be 
already defined.


Several other posts here have listed their favorites.  Notepad ++ seems 
to be a favorite.  I use HTML-kit Tools as my developing environment. 
Handles highlighting for php, html and js, as well as project 
organization.  Also includes an ftp engine to allow me to modify, upload 
and then go test my code very quickly. (I don't run php or apache locally.)


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

Re: [PHP-DB] Re: mysql query

[Ethan Rosenberg]

On 08/22/2013 11:54 AM, Jim Giner wrote:

On 8/22/2013 9:52 AM, Jim Giner wrote:

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', $qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt $opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan




Ethan - you are simply missing two dollar signs as pointed out. Once
you correct them, if there are any more errors you should then be seeing
the message from mysqli_error.

And as for the advice to dump single quotes, I'd ignore it.  The use of
double and single quotes is a very handy feature and makes for very
readable code.  Escaping double quotes is such a royal pia and makes for
more trouble deciphering code later on.  The sample you provided for us
is some of the best and most understandable code you've ever showed us.

Also - Ethan - if you used an editor that was designed for php you 
probably would have seen these missing $ signs since a good one would 
highlight php syntax and the lack of the $ would have produced a 
different color than you expected.



Jim -

I  used Netbeans.  All it said is "variable unused is scope", which is 
a  error that I often find does not mean anything.  I am as pressurized 
as you are.  Any suggestions as to an editor?


Ethan



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

Re: [PHP-DB] Re: mysql query

[Ethan Rosenberg, PhD]

On 08/22/2013 09:51 AM, Jim Giner wrote:

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', $qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt $opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan



Ethan - you are simply missing two dollar signs as pointed out. Once 
you correct them, if there are any more errors you should then be 
seeing the message from mysqli_error.


And as for the advice to dump single quotes, I'd ignore it.  The use 
of double and single quotes is a very handy feature and makes for very 
readable code.  Escaping double quotes is such a royal pia and makes 
for more trouble deciphering code later on.  The sample you provided 
for us is some of the best and most understandable code you've ever 
showed us.

Jim -

Thanks for the complement.

Ethan


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

Re: [PHP-DB] Re: mysql query

[Lester Caine]

Vinay Kannan wrote:

Jim, I know this is a stupid question to be asking this far into PHP
Development, maybe was a bit lazy, or just got too used to Notepad++, which
editor for PHP are you using? The feature which you mentioned for a good
php editor, sounds exciting, offcourse i would be looking only at the free
ones


There are a number of options for highlighting and error checking just about 
every language. Running Linux most of them are free ;)
gedit and kwrite highlight automatically and help identify problems. In the past 
I've been running on both linux and windows so something cross platform was 
essential, and it's still nice when I do have to worry about windows sites. 
Eclipse provides that base, and while PDT is the official plugin for PHP, I'm 
back on the older PHPEclipse as it fits much better with the way I work. With 
properly commented libraries it provides pop-up crib sheets onthe parameters for 
a selected function, and of cause the auto complete can be configured to match 
your preferred way of working ... I'm still on tabs for indenting


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

Re: [PHP-DB] Re: mysql query

[Vinay Kannan]

Thanks Toby, Using Notepad ++ with the language selected to PHP, the syntax
coloring is on


On Thu, Aug 22, 2013 at 11:00 PM, Toby Hart Dyke  wrote:

>
> Notepad++ will do syntax highlighting. Go to Language > P > PHP with a PHP
> file open, and see the colours change! It should be automatic - are you
> using something other than 'php' as a file extension?
>
>   Toby
>
>
> On 8/22/2013 5:27 PM, Vinay Kannan wrote:
>
>> Jim, I know this is a stupid question to be asking this far into PHP
>> Development, maybe was a bit lazy, or just got too used to Notepad++,
>> which
>> editor for PHP are you using? The feature which you mentioned for a good
>> php editor, sounds exciting, offcourse i would be looking only at the free
>> ones :D
>>
>>
>> On Thu, Aug 22, 2013 at 9:24 PM, Jim Giner 
>> **wrote:
>>
>>
>
>>>   Also - Ethan - if you used an editor that was designed for php you
>>> probably would have seen these missing $ signs since a good one would
>>> highlight php syntax and the lack of the $ would have produced a
>>> different
>>> color than you expected.
>>>
>>>
>>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] Re: mysql query

[Toby Hart Dyke]

Notepad++ will do syntax highlighting. Go to Language > P > PHP with a 
PHP file open, and see the colours change! It should be automatic - are 
you using something other than 'php' as a file extension?


  Toby

On 8/22/2013 5:27 PM, Vinay Kannan wrote:

Jim, I know this is a stupid question to be asking this far into PHP
Development, maybe was a bit lazy, or just got too used to Notepad++, which
editor for PHP are you using? The feature which you mentioned for a good
php editor, sounds exciting, offcourse i would be looking only at the free
ones :D


On Thu, Aug 22, 2013 at 9:24 PM, Jim Giner wrote:





  Also - Ethan - if you used an editor that was designed for php you
probably would have seen these missing $ signs since a good one would
highlight php syntax and the lack of the $ would have produced a different
color than you expected.





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

Re: [PHP-DB] Re: mysql query

[Vinay Kannan]

Jim, I know this is a stupid question to be asking this far into PHP
Development, maybe was a bit lazy, or just got too used to Notepad++, which
editor for PHP are you using? The feature which you mentioned for a good
php editor, sounds exciting, offcourse i would be looking only at the free
ones :D


On Thu, Aug 22, 2013 at 9:24 PM, Jim Giner wrote:

> On 8/22/2013 9:52 AM, Jim Giner wrote:
>
>> On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:
>>
>>> Dear List -
>>>
>>> I can't figure this out
>>>
>>> mysql> describe Inventory;
>>> +-+-+-**-+-+-+---+
>>> | Field   | Type| Null | Key | Default | Extra |
>>> +-+-+-**-+-+-+---+
>>> | UPC | varchar(14) | YES  | | NULL |   |
>>> | quant   | int(5)  | NO   | | NULL |   |
>>> | manuf   | varchar(20) | YES  | | NULL |   |
>>> | item| varchar(50) | YES  | | NULL |   |
>>> | orderpt | tinyint(4)  | NO   | | NULL |   |
>>> | ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
>>> | stock   | int(3)  | YES  | | NULL |   |
>>> +-+-+-**-+-+-+---+
>>>
>>> Here are code snippets -
>>>
>>>$upc   = $_SESSION['UPC'];
>>>$qnt   = $_POST['quant'];
>>>$mnf   = $_POST['manuf'];
>>>$itm   = $_POST['item'];
>>>$odrpt = $_POST['oderpt'];
>>>$opf   = $_POST['ordrpt_flag'];
>>>$stk= $_POST['stock'];
>>>
>>>$sql2 = "insert into Inventory (UPC, quant,
>>> manuf, item, orderpt, ordrpt_flag, stock)"
>>>  ."values ('$upc', $qnt,'$mnf','$itm',
>>> odrpt, 0, $stk)";
>>>$result2 = mysqli_query(cxn, $sql2);
>>>echo '$sql2';
>>>print_r($sql2);
>>>echo "$upc $qnt $mnf $itm $odrpt $opf
>>> $stk";
>>>if (!$result2)
>>>  die('Could not enter data: ' .
>>> mysqli_error());
>>>
>>> The mysql query fails.  I cannot figure out why.  It works from the
>>> command line.
>>>
>>> TIA
>>>
>>> Ethan
>>>
>>>
>>>
>>>  Ethan - you are simply missing two dollar signs as pointed out.  Once
>> you correct them, if there are any more errors you should then be seeing
>> the message from mysqli_error.
>>
>> And as for the advice to dump single quotes, I'd ignore it.  The use of
>> double and single quotes is a very handy feature and makes for very
>> readable code.  Escaping double quotes is such a royal pia and makes for
>> more trouble deciphering code later on.  The sample you provided for us
>> is some of the best and most understandable code you've ever showed us.
>>
>>  Also - Ethan - if you used an editor that was designed for php you
> probably would have seen these missing $ signs since a good one would
> highlight php syntax and the lack of the $ would have produced a different
> color than you expected.
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

[PHP-DB] Re: mysql query

[Jim Giner]

On 8/22/2013 9:52 AM, Jim Giner wrote:

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', $qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt $opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan




Ethan - you are simply missing two dollar signs as pointed out.  Once
you correct them, if there are any more errors you should then be seeing
the message from mysqli_error.

And as for the advice to dump single quotes, I'd ignore it.  The use of
double and single quotes is a very handy feature and makes for very
readable code.  Escaping double quotes is such a royal pia and makes for
more trouble deciphering code later on.  The sample you provided for us
is some of the best and most understandable code you've ever showed us.

Also - Ethan - if you used an editor that was designed for php you 
probably would have seen these missing $ signs since a good one would 
highlight php syntax and the lack of the $ would have produced a 
different color than you expected.


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

[PHP-DB] Re: mysql query

[Jim Giner]

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', $qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt $opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan



Ethan - you are simply missing two dollar signs as pointed out.  Once 
you correct them, if there are any more errors you should then be seeing 
the message from mysqli_error.


And as for the advice to dump single quotes, I'd ignore it.  The use of 
double and single quotes is a very handy feature and makes for very 
readable code.  Escaping double quotes is such a royal pia and makes for 
more trouble deciphering code later on.  The sample you provided for us 
is some of the best and most understandable code you've ever showed us.



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

[PHP-DB] Re: mysql query

[Jim Giner]

On 8/21/2013 7:48 PM, Ethan Rosenberg wrote:

Dear List -

I can't figure this out

mysql> describe Inventory;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| UPC | varchar(14) | YES  | | NULL |   |
| quant   | int(5)  | NO   | | NULL |   |
| manuf   | varchar(20) | YES  | | NULL |   |
| item| varchar(50) | YES  | | NULL |   |
| orderpt | tinyint(4)  | NO   | | NULL |   |
| ordrpt_flag | tinyint(3)  | YES  | | NULL |   |
| stock   | int(3)  | YES  | | NULL |   |
+-+-+--+-+-+---+

Here are code snippets -

   $upc   = $_SESSION['UPC'];
   $qnt   = $_POST['quant'];
   $mnf   = $_POST['manuf'];
   $itm   = $_POST['item'];
   $odrpt = $_POST['oderpt'];
   $opf   = $_POST['ordrpt_flag'];
   $stk= $_POST['stock'];

   $sql2 = "insert into Inventory (UPC, quant,
manuf, item, orderpt, ordrpt_flag, stock)"
 ."values ('$upc', $qnt,'$mnf','$itm',
odrpt, 0, $stk)";
   $result2 = mysqli_query(cxn, $sql2);
   echo '$sql2';
   print_r($sql2);
   echo "$upc $qnt $mnf $itm $odrpt $opf
$stk";
   if (!$result2)
 die('Could not enter data: ' .
mysqli_error());

The mysql query fails.  I cannot figure out why.  It works from the
command line.

TIA

Ethan



Ethan - you are simply missing two dollar signs as pointed out.  Once 
you correct them, if there are any more errors you should then be seeing 
the message from mysqli_error.


And as for the advice to dump single quotes, I'd ignore it.  The use of 
double and single quotes is a very handy feature and makes for very 
readable code.  Escaping double quotes is such a royal pia and makes for 
more trouble deciphering code later on.  The sample you provided for us 
is some of the best and most understandable code you've ever showed us.



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

[PHP-DB] Re: Mysql PDO statement with params in HAVING problem

[Jim Giner]

On 6/27/2013 7:51 AM, Alexander Pletnev wrote:

Hi everyone, im new here, so please correct me if i created or formated
topic incorrectly.

I found a problem. I have a simple query for my needs:

 $stmt = $dbh->prepare("SELECT concat(first_name,' ',last_name) as
 full_name,t.* FROM `specialists` `t` HAVING full_name like '%john%'");
 $stmt->execute();
 while($row = $stmt->fetch())
 {
echo ''; var_dump($row); echo '';
 }

It works fine, until i add a param to my query:

 $stmt = $dbh->prepare("SELECT concat(first_name,' ',last_name) as
 full_name,t.* FROM `specialists` `t` HAVING full_name like '%:query%'");
 $stmt->execute();
 $query = 'londo';
 $stmt->bindParam(':query',$query);
 while($row = $stmt->fetch())
 {
echo ''; var_dump($row); echo '';
 }

Now there is nothing in fetch(). Is it a bug ?

Thanks.


First - you prepare the query
Second - you create and 'bind' the parameters to it
Third - you execute the now completed query
Fourth - you process the results.

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

[PHP-DB] Re: MySQL, stored procedures, SIGNAL/RESIGNAL

[Marco Baumgartl]

Just for the archive: problem was caused because the query returned 
multiple result sets (which is not supported by PDO).


If the stored procedures only return one result set, everything works fine.

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

[PHP-DB] Re: mysql COUNT row results

[Jim Giner]

If you're looking for a count of ALL records, why do you want to append that 
to EACH record?

Now if you're looking for a count of subscribers who have certain attributes 
(ie, unique groups of subscribers), then you would use a group by clause and 
include the attributes that define the group in your selection.



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

[PHP-DB] Re: mysql COUNT row results

[Ron Piggott]

-Original Message- 
From: Geoff Lane

Sent: Thursday, June 23, 2011 2:15 AM
To: Ron Piggott
Cc: php-db@lists.php.net
Subject: Re: mysql COUNT row results

Hi Ron,

On Thursday, June 23, 2011, 6:14:38 AM, you wrote:



Is there a way that



SELECT COUNT(auto_increment)  as total_subscribers , `email` FROM `table`



may exist within the same query and provide more than 1 row of
search results? When I run a query like this the COUNT portion of
the result is allowing only 1 to be selected. My desire is to have
the the COUNT result appended to each row.



Thoughts anyone?  Ron


As you noticed, the aggregate function COUNT() means that you get a
table with only one row, so you need to join that table to the one
that it aggregates.

So something like:

SELECT A.total_subscribers, B.email
FROM table B,
(SELECT COUNT(auto_increment) AS total_subscribers FROM table) AS A

Should do the trick.

HTH,



Geoff thank you so much.  This worked.  Ron 



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

[PHP-DB] Re: mysql COUNT row results

[Geoff Lane]

Hi Ron,

On Thursday, June 23, 2011, 6:14:38 AM, you wrote:


> Is there a way that 

> SELECT COUNT(auto_increment)  as total_subscribers , `email` FROM `table` 

> may exist within the same query and provide more than 1 row of
> search results? When I run a query like this the COUNT portion of
> the result is allowing only 1 to be selected. My desire is to have
> the the COUNT result appended to each row.

> Thoughts anyone?  Ron

As you noticed, the aggregate function COUNT() means that you get a
table with only one row, so you need to join that table to the one
that it aggregates.

So something like:

SELECT A.total_subscribers, B.email
FROM table B,
(SELECT COUNT(auto_increment) AS total_subscribers FROM table) AS A

Should do the trick.

HTH,

-- 
Geoff Lane
Cornwall, UK
ge...@gjctech.co.uk


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

[PHP-DB] Re: MySQL Wildcard

[David Robley]

Karl DeSaulniers wrote:

> Hello All,
> What I want to do is get all the fields from a table without
> specifying which ID to grab from.
> I am trying to implement a wildcard in my query. What am I doing
> wrong? I keep getting...
> 
> "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
> result resource"
> 
> CODE:
> 
> function getOptGrps($ID){
>if ($ID == "All") {
>   $ID = "%"; // % = MySQL multi character wildcard
>}
>$q = "SELECT * FROM ".OPT_GRPS_TABLE." WHERE OptGrpID LIKE
> '$ID' ";
>$result = $this->query($q);
>/* Error occurred, return given name by default */
>if(!$result || (mysql_numrows($result) < 1)){
>   return NULL;
>}
>/* Return result array */
>$array_results = mysql_fetch_array($result);
>return $array_results;
> }
> 
> AND this does not work either
> 
> function getOptGrps(){
>$q = "SELECT * FROM ".OPT_GRPS_TABLE;
>$result = $this->query($q);
>/* Error occurred, return given name by default */
>if(!$result || (mysql_numrows($result) < 1)){
>   return NULL;
>}
>/* Return result array */
>$array_results = mysql_fetch_array($result);
>return $array_results;
> }
> 
> TIA,

Well, I'd hazard a guess that OPT_GRPS_TABLE may not be what you think it
is; in any case, the first step in debugging would be to echo your query $q
and see what it really contains. And does the class that handles your db
query have an error reporting function? If so, try using it.

Note that to retrieve all records, you could skip the WHERE, or use WHERE 1

Cheers
-- 
David Robley

Machine-independent: does not run on any existing machine.
Today is Pungenday, the 55th day of Discord in the YOLD 3176. 


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

[PHP-DB] Re: MySQL Conditional Trigger

[Dee Ayy]

On Fri, Oct 31, 2008 at 11:09 AM, Dee Ayy <[EMAIL PROTECTED]> wrote:
> ...
> IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN
> ...

The following seems to work.  Is it correct and not too verbose?
IF (OLD.StatusShipDate IS NULL AND NEW.StatusShipDate IS NOT NULL) OR
(OLD.StatusShipDate != NEW.StatusShipDate AND NEW.StatusShipDate IS
NOT NULL) THEN

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

[PHP-DB] Re: [PHP] Re: [PHP-DB] Re: MySQL Identifying worst-performing codes

[Mark Summers]

There may be something useful here...

http://forge.mysql.com/

chris smith wrote:
> On Nov 9, 2007 1:18 AM, Colin Guthrie <[EMAIL PROTECTED]> wrote:
>   
>> Lasitha Alawatta wrote:
>> 
>>> There is  a tool call "idera" (SQL diagnostic manager). Basically it is
>>> a performance monitoring and diagnostics tool.
>>>
>>> It has a feature;
>>> Identifying of worst-performing codes –
>>>
>>> Identifies performance bottlenecks such as the worst-performing stored
>>> procedures, long-running queries, most frequently run queries, SQL
>>> Statements and SQL batches
>>>
>>> http://www.idera.com/Products/SQLdm/Features.aspx
>>>
>>>
>>> I'm looking for a same like tool for MySQL. Is anyone have any  ideas.
>>>   
>> I know this is OT for this list but.
>>
>> In addition to the slow query logging I mentioned before (which you
>> seemed to appreciate :)), I've just stumbled across this:
>> http://rackerhacker.com/mysqltuner/
>> 
>
> http://jeremy.zawodny.com/mysql/mytop/ might come in handy too.
>
>   

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

Re: [PHP-DB] Re: MySQL Identifying worst-performing codes

[chris smith]

On Nov 9, 2007 1:18 AM, Colin Guthrie <[EMAIL PROTECTED]> wrote:
> Lasitha Alawatta wrote:
> > There is  a tool call "idera" (SQL diagnostic manager). Basically it is
> > a performance monitoring and diagnostics tool.
> >
> > It has a feature;
> > Identifying of worst-performing codes –
> >
> > Identifies performance bottlenecks such as the worst-performing stored
> > procedures, long-running queries, most frequently run queries, SQL
> > Statements and SQL batches
> >
> > http://www.idera.com/Products/SQLdm/Features.aspx
> >
> >
> > I'm looking for a same like tool for MySQL. Is anyone have any  ideas.
>
>
> I know this is OT for this list but.
>
> In addition to the slow query logging I mentioned before (which you
> seemed to appreciate :)), I've just stumbled across this:
> http://rackerhacker.com/mysqltuner/

http://jeremy.zawodny.com/mysql/mytop/ might come in handy too.

-- 
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] Re: MySQL Identifying worst-performing codes

[Colin Guthrie]

Lasitha Alawatta wrote:
> There is  a tool call “idera” (SQL diagnostic manager). Basically it is
> a performance monitoring and diagnostics tool.
> 
> It has a feature; 
> Identifying of worst-performing codes –
> 
> Identifies performance bottlenecks such as the worst-performing stored
> procedures, long-running queries, most frequently run queries, SQL
> Statements and SQL batches
> 
> http://www.idera.com/Products/SQLdm/Features.aspx
>  
> 
> I’m looking for a same like tool for MySQL. Is anyone have any  ideas.


I know this is OT for this list but.

In addition to the slow query logging I mentioned before (which you
seemed to appreciate :)), I've just stumbled across this:
http://rackerhacker.com/mysqltuner/

HTH some people.

Col

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

RE: [PHP-DB] Re: MySQL Identifying worst-performing codes

[Lasitha Alawatta]

Dear Col,


I'm highly appreciate your reply.



Best Regards,
Lasitha


-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Colin Guthrie
Sent: Thursday, October 04, 2007 7:47 PM
To: php-db@lists.php.net
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [PHP-DB] Re: MySQL Identifying worst-performing codes

Lasitha Alawatta wrote:
> There is  a tool call “idera” (SQL diagnostic manager). Basically it is
> a performance monitoring and diagnostics tool.
> 
> It has a feature; 
> 
>  
> 
> Identifying of worst-performing codes –
> 
> Identifies performance bottlenecks such as the worst-performing stored
> procedures, long-running queries, most frequently run queries, SQL
> Statements and SQL batches

You can just turn on the MySQL option "log slow queries" and it logs
them for you. Make those more efficient and you've already taken care of
a lot of the low hanging fruit for db optimisation.

Col.

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


DOTW DISCLAIMER:

This e-mail and any attachments are strictly confidential and intended for the 
addressee only. If you are not the named addressee you must not disclose, copy 
or take
any action in reliance of this transmission and you should notify us as soon as 
possible. If you have received it in error, please contact the message sender 
immediately.
This e-mail and any attachments are believed to be free from viruses but it is 
your responsibility to carry out all necessary virus checks and DOTW accepts no 
liability
in connection therewith. 

This e-mail and all other electronic (including voice) communications from the 
sender's company are for informational purposes only.  No such communication is 
intended
by the sender to constitute either an electronic record or an electronic 
signature or to constitute any agreement by the sender to conduct a transaction 
by electronic means.

[PHP-DB] Re: MySQL Identifying worst-performing codes

[Colin Guthrie]

Lasitha Alawatta wrote:
> There is  a tool call “idera” (SQL diagnostic manager). Basically it is
> a performance monitoring and diagnostics tool.
> 
> It has a feature; 
> 
>  
> 
> Identifying of worst-performing codes –
> 
> Identifies performance bottlenecks such as the worst-performing stored
> procedures, long-running queries, most frequently run queries, SQL
> Statements and SQL batches

You can just turn on the MySQL option "log slow queries" and it logs
them for you. Make those more efficient and you've already taken care of
a lot of the low hanging fruit for db optimisation.

Col.

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

Re: [PHP-DB] Re: mysql statement [SOLVED]

[Jas]

Thanks, most of that I knew but the grouping in your first example is
the one that allowed me to only use records matching any field but still
limited by the one db field. So thanks again. I was not aware of the
parenthesis for grouping objects like a mathematical equation where
anything inside the paren's gets executed first and still conditional on
the remainder of the equation.

TG wrote:
> Couple of little pointers.
> 
> If you're doing the sub-select, then you don't need the "group like 'mac'" 
> because you've already limited your query in the subselect to a specific 
> groupname.
> 
> The subselect is probably unnecessary since what you're doing is relatively 
> simple and uses the same table.  It probably just adds overhead.  But in 
> relation to that, you need to segregate your OR statements from your final 
> AND statement or it won't limit by group properly.
> 
> WHERE ( `ordernum` LIKE "35132"
> OR `price` LIKE "35132"
> OR `partnum` LIKE "35132"
> OR `vendor` LIKE "35132"
> OR `purpose` LIKE "35132"
> OR `tracking` LIKE "35132"
> OR `contact` LIKE "35132" )
> AND `group` LIKE 'mac'
> 
> Notice the ( and ).
> 
> Next pointer..  LIKE is used when you're doing a non-exact search, but you 
> haven't used any wildcards to indicate a partial search.   So what you're 
> essentially doing is the same as "price = 35132".If you're doing a 
> multi-field search and want to use LIKE, you'd do this on each:
> 
> `price` LIKE "%35132%"
> 
> The % is sorta like * in other systems.  Any quantity of any characters can 
> match that space.  Or you can do`price` LIKE "35132%" if you want to 
> search the beginning of the field (note the % at the end, but not the 
> beginning of the search string this time).
> 
> One last thing... I don't know if it increases speed or not, but since you're 
> using so many fields this MAY speed up the query.Depends on how much 
> data you're searching through and if you're doing %search% or need to find 
> start/end strings.
> 
> Anyway, here's the test..  what's faster, what you're trying to do:
> 
> WHERE ( `ordernum` LIKE "35132" OR `price` LIKE "35132" OR `partnum` LIKE 
> "35132"
>  OR `vendor` LIKE "35132" OR `purpose` LIKE "35132" OR 
> `tracking` LIKE "35132"
>  OR `contact` LIKE "35132" )
> AND `group` LIKE 'mac'
> 
> or something like this
> 
> WHERE CONCAT(`ordernum`, `price`, `partnum`, `vendor`, `purpose`, `tracking`, 
> `contact`) LIKE "%35132%"
> AND `group` LIKE '%mac%'
> 
> 
> All the LIKE comparisons against a ton of data MAY be more taxing on the 
> server than doing a CONCAT of all the fields then doing a single LIKE.   If 
> you're doing LIKE %search% where it can appear anywhere in any of the 
> fields, then CONCAT + LIKE would work just as good as LIKE OR LIKE OR ...  
> results-wise.   I don't know if it's faster/less intensive or not though.  
> You'd have to do some tests.
> 
> Also, comparisons like "=" should (if I recall) be faster than LIKE 
> comparisons.  So if you really meant to use "=", do that instead.
> 
> Let me know if any of that's unclear.  I know I get some kooky ideas 
> sometimes.  Also, anyone see anything I screwed up or have thoughts on this 
> matter?
> 
> Good luck!
> 
> -TG
> 
> 
> 
> 
> 
> 
> - Original Message -
> From: Jas <[EMAIL PROTECTED]>
> To: php-db@lists.php.net
> Date: Wed, 26 Sep 2007 12:08:53 -0600
> Subject: [PHP-DB] Re: mysql statement [SOLVED]
> 
>> Got if figured out, needed a sub-select type of query:
>>
>> mysql>  SELECT *
>>  -> FROM ( SELECT * FROM `orders`
>>  -> WHERE `group` = "groupname" )
>>  -> AS orders UNION SELECT * FROM `orders`
>>  -> WHERE `ordernum` LIKE "35132"
>>  -> OR `price` LIKE "35132"
>>  -> OR `partnum` LIKE "35132"
>>  -> OR `vendor` LIKE "35132"
>>  -> OR `purpose` LIKE "35132"
>>  -> OR `tracking` LIKE "35132"
>>  -> OR `contact` LIKE "35132"
>>  -> AND `group` LIKE 'mac'
>>  -> ORDER BY `ordernum`
>>  -> LIMIT 0 , 30;

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

Re: [PHP-DB] Re: mysql statement [SOLVED]

[TG]

Couple of little pointers.

If you're doing the sub-select, then you don't need the "group like 'mac'" 
because you've already limited your query in the subselect to a specific 
groupname.

The subselect is probably unnecessary since what you're doing is relatively 
simple and uses the same table.  It probably just adds overhead.  But in 
relation to that, you need to segregate your OR statements from your final 
AND statement or it won't limit by group properly.

WHERE ( `ordernum` LIKE "35132"
OR `price` LIKE "35132"
OR `partnum` LIKE "35132"
OR `vendor` LIKE "35132"
OR `purpose` LIKE "35132"
OR `tracking` LIKE "35132"
OR `contact` LIKE "35132" )
AND `group` LIKE 'mac'

Notice the ( and ).

Next pointer..  LIKE is used when you're doing a non-exact search, but you 
haven't used any wildcards to indicate a partial search.   So what you're 
essentially doing is the same as "price = 35132".If you're doing a 
multi-field search and want to use LIKE, you'd do this on each:

`price` LIKE "%35132%"

The % is sorta like * in other systems.  Any quantity of any characters can 
match that space.  Or you can do`price` LIKE "35132%" if you want to 
search the beginning of the field (note the % at the end, but not the 
beginning of the search string this time).

One last thing... I don't know if it increases speed or not, but since you're 
using so many fields this MAY speed up the query.Depends on how much 
data you're searching through and if you're doing %search% or need to find 
start/end strings.

Anyway, here's the test..  what's faster, what you're trying to do:

WHERE ( `ordernum` LIKE "35132" OR `price` LIKE "35132" OR `partnum` LIKE 
"35132"
 OR `vendor` LIKE "35132" OR `purpose` LIKE "35132" OR 
`tracking` LIKE "35132"
 OR `contact` LIKE "35132" )
AND `group` LIKE 'mac'

or something like this

WHERE CONCAT(`ordernum`, `price`, `partnum`, `vendor`, `purpose`, `tracking`, 
`contact`) LIKE "%35132%"
AND `group` LIKE '%mac%'


All the LIKE comparisons against a ton of data MAY be more taxing on the 
server than doing a CONCAT of all the fields then doing a single LIKE.   If 
you're doing LIKE %search% where it can appear anywhere in any of the 
fields, then CONCAT + LIKE would work just as good as LIKE OR LIKE OR ...  
results-wise.   I don't know if it's faster/less intensive or not though.  
You'd have to do some tests.

Also, comparisons like "=" should (if I recall) be faster than LIKE 
comparisons.  So if you really meant to use "=", do that instead.

Let me know if any of that's unclear.  I know I get some kooky ideas 
sometimes.  Also, anyone see anything I screwed up or have thoughts on this 
matter?

Good luck!

-TG






- Original Message -
From: Jas <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Date: Wed, 26 Sep 2007 12:08:53 -0600
Subject: [PHP-DB] Re: mysql statement [SOLVED]

> Got if figured out, needed a sub-select type of query:
> 
> mysql>  SELECT *
>  -> FROM ( SELECT * FROM `orders`
>  -> WHERE `group` = "groupname" )
>  -> AS orders UNION SELECT * FROM `orders`
>  -> WHERE `ordernum` LIKE "35132"
>  -> OR `price` LIKE "35132"
>  -> OR `partnum` LIKE "35132"
>  -> OR `vendor` LIKE "35132"
>  -> OR `purpose` LIKE "35132"
>  -> OR `tracking` LIKE "35132"
>  -> OR `contact` LIKE "35132"
>  -> AND `group` LIKE 'mac'
>  -> ORDER BY `ordernum`
>  -> LIMIT 0 , 30;

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

[PHP-DB] Re: mysql statement [SOLVED]

[Jas]

Got if figured out, needed a sub-select type of query:

mysql>  SELECT *
 -> FROM ( SELECT * FROM `orders`
 -> WHERE `group` = "groupname" )
 -> AS orders UNION SELECT * FROM `orders`
 -> WHERE `ordernum` LIKE "35132"
 -> OR `price` LIKE "35132"
 -> OR `partnum` LIKE "35132"
 -> OR `vendor` LIKE "35132"
 -> OR `purpose` LIKE "35132"
 -> OR `tracking` LIKE "35132"
 -> OR `contact` LIKE "35132"
 -> AND `group` LIKE 'mac'
 -> ORDER BY `ordernum`
 -> LIMIT 0 , 30;

Jas wrote:
> I am looking for some advice on how to achieve something and so far have
> been unable to do what I am looking to do.
> 
> Here is the query I am using:
> mysql>  SELECT *
> -> FROM `orders`
> -> WHERE `ordernum` LIKE "35132"
> -> OR `price` LIKE "35132"
> -> OR `partnum` LIKE "35132"
> -> OR `vendor` LIKE "35132"
> -> OR `purpose` LIKE "35132"
> -> OR `tracking` LIKE "35132"
> -> OR `contact` LIKE "35132"
> -> AND `group` LIKE 'mac'
> -> ORDER BY `ordernum`
> -> LIMIT 0 , 30;
> 
> First here is the table structure:
> mysql> describe orders;
> +-+--+--+-+-++
> | Field   | Type | Null | Key | Default | Extra  |
> +-+--+--+-+-++
> | id  | int(255) | NO   | PRI | | auto_increment |
> | ordernum| int(10)  | NO   | | ||
> | date| varchar(60)  | NO   | | ||
> | time| varchar(20)  | NO   | | ||
> | group   | varchar(20)  | NO   | | ||
> | quantity| int(10)  | NO   | | ||
> | description | varchar(255) | NO   | | ||
> | price   | decimal(3,0) | NO   | | ||
> | partnum | varchar(40)  | NO   | | ||
> | vendor  | varchar(65)  | NO   | | ||
> | purpose | varchar(255) | NO   | | ||
> | tracking| varchar(120) | NO   | | ||
> | contact | varchar(255) | NO   | | ||
> | eta | varchar(50)  | NO   | | ||
> | department  | varchar(125) | NO   | | ||
> | notes   | varchar(255) | NO   | | ||
> +-+--+--+-+-++
> 16 rows in set (0.00 sec)
> 
> I am trying to essentially LIMIT all records returned to be limited by
> the `group` field so I can search for records and limit the rows
> returned by that one field.
> 
> Any tips? TIA.
> jas

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

Re: [PHP-DB] Re: MySQL- Stored Procedures & Views

[Chris]

Haydar TUNA wrote:

Hello,
Stored Procedures are faster than standart sql query beacuse they have 
already compiled sql script.


I wouldn't say that's the case every single time - personally I'd do 
some testing to see what works better.


--
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] Re: MySQL- Stored Procedures & Views

[Haydar TUNA]

Hello,
Stored Procedures are faster than standart sql query beacuse they have 
already compiled sql script.


-- 
Republic Of Turkey - Ministry of National Education
Education Technology Department Ankara / TURKEY
Web: http://www.haydartuna.net 
  ""Lasitha Alawatta"" <[EMAIL PROTECTED]>, haber iletisinde sunlari 
yazdi:[EMAIL PROTECTED]
  Hi All, 

   

  Any body has experience using MySQL Stored Procedures & Views with PHP. 

  How is the performance of using SPs in mysql. 

   

   

   

  Lasitha Alawatta

  Application Developer

  Destinations of the World Holding Establishment

  P O Box: 19950

  Dubai, United Arab Emirates

   




DOTW DISCLAIMER:

This e-mail and any attachments are strictly confidential and intended 
for the addressee only. If you are not the named addressee you must not 
disclose, copy or take
any action in reliance of this transmission and you should notify us as 
soon as possible. If you have received it in error, please contact the message 
sender immediately.
This e-mail and any attachments are believed to be free from viruses 
but it is your responsibility to carry out all necessary virus checks and DOTW 
accepts no liability
in connection therewith. 

This e-mail and all other electronic (including voice) communications 
from the sender's company are for informational purposes only.  No such 
communication is intended
by the sender to constitute either an electronic record or an 
electronic signature or to constitute any agreement by the sender to conduct a 
transaction by electronic means.
   

RE: [PHP-DB] Re: MySQL 0 index date and time functions (mode or typo)?

[Dwight Altman]

Yes, that table is self-explanatory for WEEK.

I was asking about DAYOFMONTH and MONTH returning 0, since there does indeed
exist an explanation for WEEK returning a 0.

Regards,
Dwight
> -Original Message-
> From: itoctopus [mailto:[EMAIL PROTECTED]
> Sent: Friday, May 18, 2007 6:58 AM
> To: php-db@lists.php.net
> Subject: [PHP-DB] Re: MySQL 0 index date and time functions (mode or
> typo)?
> 
> Hey,
> I think this table is self-explanatory:
> 
>   First day
>   Mode of week Range Week 1 is the first week .
>   0 Sunday 0-53 with a Sunday in this year
>   1 Monday 0-53 with more than 3 days this year
>   2 Sunday 1-53 with a Sunday in this year
>   3 Monday 1-53 with more than 3 days this year
>   4 Sunday 0-53 with more than 3 days this year
>   5 Monday 0-53 with a Monday in this year
>   6 Sunday 1-53 with more than 3 days this year
>   7 Monday 1-53 with a Monday in this year
> 
> 
> --
> itoctopus - http://www.itoctopus.com
> ""Dwight Altman"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > In MySQL, regardless of the documentation version,
> > http://dev.mysql.com/doc/refman/4.1/en/date-and-time-
> functions.html#function
> > _week I've seen the explanation for receiving a 0 from the WEEK function
> > depending on the mode setting which causes a range between 0-53.
> >
> > But what about:
> > DAYOFMONTH 0 to 31
> > MONTH 0 to 12
> >
> > I was wondering if 0 may be for invalid dates, but I get NULL for the
> > following invalid date:
> > SELECT MONTH( '1998-14-03' ), DAYOFMONTH( '1998-14-03' )
> >
> > When might I receive a 0 for these two functions?
> >
> >
> > Regards,
> > Dwight
> 
> --
> 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] Re: MySQL 0 index date and time functions (mode or typo)?

[itoctopus]

Hey,
I think this table is self-explanatory:

  First day
  Mode of week Range Week 1 is the first week .
  0 Sunday 0-53 with a Sunday in this year
  1 Monday 0-53 with more than 3 days this year
  2 Sunday 1-53 with a Sunday in this year
  3 Monday 1-53 with more than 3 days this year
  4 Sunday 0-53 with more than 3 days this year
  5 Monday 0-53 with a Monday in this year
  6 Sunday 1-53 with more than 3 days this year
  7 Monday 1-53 with a Monday in this year


-- 
itoctopus - http://www.itoctopus.com
""Dwight Altman"" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> In MySQL, regardless of the documentation version,
> http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function
> _week I've seen the explanation for receiving a 0 from the WEEK function
> depending on the mode setting which causes a range between 0-53.
>
> But what about:
> DAYOFMONTH 0 to 31
> MONTH 0 to 12
>
> I was wondering if 0 may be for invalid dates, but I get NULL for the
> following invalid date:
> SELECT MONTH( '1998-14-03' ), DAYOFMONTH( '1998-14-03' )
>
> When might I receive a 0 for these two functions?
>
>
> Regards,
> Dwight 

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

Re: [PHP-DB] Re: MySQL Query on the Fly

[Steven Cruz]

You can the entire thing in Java script with Style sheets. Depends on 
what he wants to do. iframes could be far simpler. :)



itoctopus wrote:

You have to use an iframe to do what you want. Once the user select
something, you referesh the iframe and you pass specific parameters to it
based on the user's choice.

  


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

[PHP-DB] Re: MySQL Query on the Fly

[itoctopus]

You have to use an iframe to do what you want. Once the user select
something, you referesh the iframe and you pass specific parameters to it
based on the user's choice.

-- 
itoctopus - http://www.itoctopus.com
""Todd A. Dorschner"" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
Good Morning,



I've been searching and toying with this solution for some time but
can't find that right answer.  Looking for solutions/suggestions to the
following:



I created a program that will allow people to track sales and depending
on what they've sold, they will either get a set bonus, or a bonus based
on a percentage.  I have no problem creating a drop down box for them to
select from, and I could probably create a select button that would do
the action, but I'd rather have it in one step.  So what I'm trying to
do is somehow when they select from the drop down (via a JavaScript
onchange or something) I'd like it to pull data from the MySQL database.
I'd like it to pull the amount and then each or per based on the ID of
what they've selected from the drop down.  If it was always based on
each sale or based on a percentage, that would be easy as I could code
that into the drop box, but I'd like to keep it based on the ID so that
it can pull out of the database the name, and then when changed pull the
info.  Any ideas?  Thanks in advance for the help.  If you have any
questions or it seems like I left something out, please let me know.





Thank you for your time,


Todd Dorschner

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

[PHP-DB] Re: mysql question.

[Dan Luevano]

Me2resh Lists,

with my limited SQL experience it appears that you're asking to sort the 
query without the proper results from the query. Your SELECT statement 
below should return the following:


Non-duplicate rows of mena_guests.EMail where mena_guests.Voted is yes, 
starting at $startingID up to $items_numbers_list records.


Since the DISTINCT clause is used, only non-duplicate records are 
returned, and there is no count of the rows duplicated for each distinct 
EMail. Thus you can't sort the result by any count. I think the select 
statement that you really want is this...


SELECT EMail, COUNT(*) FROM mena_guests WHERE Voted = 'yes' GROUP BY 
EMail LIMIT $startingID, $items_numbers_list



The "group by" will cause the DISTINCT effect AND you'll be able to see 
how many duplicates there were for each distinct EMail.


Hope this helps,

Dan Luevano

[EMAIL PROTECTED] wrote:


mysql question.
43402 by: Me2resh Lists

hi

i need help regarding a sql query in my php app.



the query is :

   $SQL = "SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes'

LIMIT $startingID,$items_numbers_list";



i want to sort this query by the number of the repeated EMail counts.

can anyone help me with that please ?


--
  


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

[PHP-DB] Re: MySQL Foreign Key Issue

[Roberto Mansfield]

Are you using InnoDB for you tables?

Lasitha Alawatta wrote:
> Hello,
> 
>  
> 
> I have 2 issue, regarding MySQL “Foreign Key”.
> 
> I have two tables;
> 
> Table 01 */CUSTOMER/*
> 
> column name
> 
>   
> 
> characteristic
> 
> SID
> 
>   
> 
> Primary Key
> 
> Full_Name
> 
>   
> 
>  
> 
>  
> 
> Table */ORDERS/*
> 
> column name
> 
>   
> 
> characteristic
> 
> Order_ID
> 
>   
> 
> Primary Key
> 
> Order_Date
> 
>   
> 
>  
> 
> Customer_SID
> 
>   
> 
> Foreign Key
> 
> Amount
> 
>   
> 
>  
> 
>  
> 
> When I run “ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES
> CUSTOMER(SID);” that sql statement,
> 
>  
> 
> I inserted 2 records to both tables.
> 
> _ _
> 
> _Question No. 01._
> 
> Then I removed 1 record from CUSTOMER table. But It want give any error
> message. It should give an error message, because in ORDERS table
> already have some records relevant to the deleted customer record in
> CUSTOMER table.
> 
>  
> 
> Question is why it want give any error ? (I’m using phpMyAdmin)
> 
>  
> 
> _Question No. 02._
> 
> Is there any tool available for to get a Database diagram (like in MS
> SQL Server). I found a tool call “DBDesigner 4”. But it wants show the
> DB Diagram.
> 
>  
> 
>  
> 
> Thanks in Advance,
> 
> Lasitha.
> 
>  
> 
>  
> 
> 
> 
> 
> DOTW DISCLAIMER:
> 
> This e-mail and any attachments are strictly confidential and intended for 
> the addressee only. If you are not the named addressee you must not disclose, 
> copy or take
> any action in reliance of this transmission and you should notify us as soon 
> as possible. If you have received it in error, please contact the message 
> sender immediately.
> This e-mail and any attachments are believed to be free from viruses but it 
> is your responsibility to carry out all necessary virus checks and DOTW 
> accepts no liability
> in connection therewith. 
> 
> This e-mail and all other electronic (including voice) communications from 
> the sender's company are for informational purposes only.  No such 
> communication is intended
> by the sender to constitute either an electronic record or an electronic 
> signature or to constitute any agreement by the sender to conduct a 
> transaction by electronic means.
> 

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

RE: [PHP-DB] re: mysql ORDER BY problems

[Dwight Altman]

I believe I did this one time without changing the database schema; simply
by just putting the type in the ORDER BY clause.

Something like
ORDER BY BINARY numbers ASC
or maybe
ORDER BY CAST(numbers AS BINARY) ASC

Regards,
Dwight

> -Original Message-
> From: J. Alejandro Ceballos Z. -JOAL-
> [mailto:[EMAIL PROTECTED]
> Sent: Sunday, June 18, 2006 7:01 PM
> To: php-db@lists.php.net
> Cc: [EMAIL PROTECTED]
> Subject: [PHP-DB] re: mysql ORDER BY problems
> 
> are they defined as integer or varchars (string)?
> 
> try
>   a) exchange field definition varchar / integer
>   b) order by length(number) and later by number
> 
> --

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

[PHP-DB] re: mysql ORDER BY problems

[J. Alejandro Ceballos Z. -JOAL-]

are they defined as integer or varchars (string)?

try
 a) exchange field definition varchar / integer
 b) order by length(number) and later by number

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

[PHP-DB] Re: MySQL Query Help

[João Cândido de Souza Neto]

Mark Dyer wrote:

> 
> Hello, I'm unsure how to write the follow query, Please can someone
> assist.
> 
> I am writing in short hand the basis of the two queries I would like to
> combine. The object is to select all the products that have not meet the
> min sale requirements so I can send myself a reminder email.
> 
> The first table products contains the product information and how ofter
> the stores must purchase and at what period ie, Weekly or Monthly by
> product.
> 
> First Query gives me all the products that must check to see if they have
> meet the min sales.
> 
> Select store_id,product_id, min_level, reoirder_period from products db
> where monthly_order_required > 0
> 
> 
> Second Query is then run to test to see if they have meet the min sales.
> 
> Php: If reorder period = weekly then backdate = 7 days elseif period =
> monthly then backdate = 30
> 
> Select sum(sale_product_qty) as sale_period_total from sales db where
> store = store_id and product = product_id and date <= now and date >
> backdate and sale_period_total > min_level
> 
> 
> Result: sum of sales in the last week or month period for that product
> from that store. If no result then sale level ok. If result then the
> difference from min_level is what is require for the store to make asap.
> 
> Php: if sale_period_total < min_level then email low order email.
> 
> 
> Any assistance to combine to the queries instead of hundreds of individual
> would be grateful.
> 
> Regards
> Mark Dyer
> NZ

Please, could you putting your real code here to us?


-- 
---
João Cândido de Souza Neto
Web Developer

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

[PHP-DB] Re: MySQL - Unable to run service

[Dusty Bin]

Ralph Brickley wrote:
> I have been using MySQL for quite some time and am now running Server 4.1. I
> have been developing custom web-based software for a customer and recently
> moved their server to a different machine. I have everything running
> smoothly EXCEPT for mySQL. Any time I try to start the service I get an
> error:
> 
> Error 1067: Unable to start service on Local Computer.

On the assumption that you are using windows, since you are using
windows terminology:
There should be a file in your mysql data directory, called something
like servername.err.  MySQL logs start-ups and shut-downs, and some
error information in this file.  Check it out, you may be able to see
the problem immediately.

By the way, this problem should probably be addressed to the MySQL fora,
as this is definitely not related to PHP

HTH...   Dusty

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

[PHP-DB] Re: MySQL - Unable to run service

[Nadim Attari]

Another link:
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Windows_upgrading.html

Regards,
Nadim Attari
Alienworkers.com

Nadim Attari wrote:
> Hello,
> 
> I'm not being rude but it is a good thing to do before coming here:
> 
> http://www.google-is-my-best-friend.com (Jusk kidding...)
> 
> Here you go
> http://www.google.com/search?hl=en&q=%22Error+1067%22+%2B+mysql&btnG=Search
> 
> Some picks:
> - http://forums.mysql.com/read.php?11,11388,11388
> - http://www.experts-exchange.com/Databases/Mysql/Q_21045378.html
> 
> And please, give more specific info about your problem...
> 
> Best Regards,
> Nadim Attari
> Alienworkers.com

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

[PHP-DB] Re: MySQL - Unable to run service

[Nadim Attari]

Hello,

I'm not being rude but it is a good thing to do before coming here:

http://www.google-is-my-best-friend.com (Jusk kidding...)

Here you go
http://www.google.com/search?hl=en&q=%22Error+1067%22+%2B+mysql&btnG=Search

Some picks:
- http://forums.mysql.com/read.php?11,11388,11388
- http://www.experts-exchange.com/Databases/Mysql/Q_21045378.html

And please, give more specific info about your problem...

Best Regards,
Nadim Attari
Alienworkers.com



Ralph Brickley wrote:
> I have been using MySQL for quite some time and am now running Server 4.1. I
> have been developing custom web-based software for a customer and recently
> moved their server to a different machine. I have everything running
> smoothly EXCEPT for mySQL. Any time I try to start the service I get an
> error:
> 
> Error 1067: Unable to start service on Local Computer.
> 
>  
> 
> Anyone have an idea about that? That user logged in is the comp
> administrator.
> 
>  
> 
>  
> 
> Ralph E. Brickley
> 
> A&E Security and Electronics
> 
> (503) 472-6439 / (877) 472-6439
> 
> (503) 519-3914 Cell
> 
> (503) 472-3570 Fax
> 
> [EMAIL PROTECTED]

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

[PHP-DB] Re: MySQL - Unable to run service

[Adriaan van Natijne]

Windows / Unix (/ Linux)?

Op Wed, 15 Mar 2006 10:34:42 -0800, schreef Ralph Brickley:

> I have been using MySQL for quite some time and am now running Server 4.1. I
> have been developing custom web-based software for a customer and recently
> moved their server to a different machine. I have everything running
> smoothly EXCEPT for mySQL. Any time I try to start the service I get an
> error:
> 
> Error 1067: Unable to start service on Local Computer.
> 
>  
> 
> Anyone have an idea about that? That user logged in is the comp
> administrator.
> 
>  
> 
>  
> 
> Ralph E. Brickley
> 
> A&E Security and Electronics
> 
> (503) 472-6439 / (877) 472-6439
> 
> (503) 519-3914 Cell
> 
> (503) 472-3570 Fax
> 
> [EMAIL PROTECTED]

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

[PHP-DB] Re: MySQL Update Quandry

[Matt Monaco]

This might be easier using the mysqli extension, but the function mysql_info 
will give you a string result indicating information about the most recent 
string, just as you would from the command line.

The rows matched is what you're looking for, which appears first in the 
string, so you should fairly easily be able to parse the string for the 
first integer (there might even be a function like get_int_value that will 
return the integer value of a string based on the first number it finds - 
PLEASE SOMEONE EXPAND ON THIS).

But either way, once you've extracted the rows matched from mysql_info your 
problem should be solved.


Matt


"maestro" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Greetings,
>
> I am doing an update to a table as such:
>
> class DB {
> 
>
>   function updateMember($email, $password, $postalCode,
> $language, $id, $word) {
>
>   ...
> if (!(@ mysql_query($query, $connection))) {
>   $this->errors = array(mysql_errno(), mysql_error());
>   $error = new Error();
>   $error->logError($this->errors);
>   return false;
> }
>
> if (mysql_affected_rows() == 0) {
>   return false;
> }
>
> return true;
>   }
> }
>
>
>
> If the user supplies a wrong $word the mysql_affected_rows will be 0 and 
> the method will return false. This is fine.
> My problem arises when the user supplies data that has not changed. The 
> mysql_affected_rows will be 0 as well.
> How can I know the difference between an update that fails due to a failed 
> where clause and one that updates nothing?
>
> TIA,
> Glenn 

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

Re: [PHP-DB] Re: MySQL Connect using Dreamweaver

[Shahmat Dahlan]

Correct me if I'm wrong, but if I'm not mistaken Mysql 5 will not work 
in Dreamweaver.


It will only work if you have 4.x (can't remember the exact version)

Even if you have installed Mysql 4.x, u must make sure you add in 
old_password at the last line of the my.cnf
If not, you can always update all the passwords for all the user 
accounts in your Mysql server by using old_password() function.


e.g.
 use mysql; update user set password=old_password('abc123') where 
host='localhost', user='root';


Blaz wrote:

Hi. You must use mysqli which is only supported in php5. Of corse if 
you will use MySQL5.


regards

W Roothman wrote:


Dear All,

I have installed MySQL 5 with PHP 4.3.3.

When trying to connect to a database form the 'Database' tab in 
Dreamweaver, I now get the following error when trying to select a DB:


'1251 Client does not support authentication protocol requested by 
server; consider upgrading MySQL client'


Regards,

Will






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

Re: [PHP-DB] Re: MySQL Connect using Dreamweaver

[skaufmann]

Will,

is mysql installed on your local machine or on a different server? Do
you work with root or with a newly created user?
If mysql is not installed on your local machine, you have to grant
access from other machines to your database. Here's an example (with a
newly created user).

Example:
your work machine's IP: 192.168.0.25
your username: will

open a mysql shell (with root/superuser privileges) and type:

GRANT ALL PRIVILEGES ON *.* TO 'will'@'192.168.0.25' IDENTIFIED BY
'mysecretpassword';

use mysql;

UPDATE user SET password = OLD_PASSWORD('mysecretpassword') WHERE user =
'will' AND host = '192.168.0.25';

FLUSH PRIVILEGES;

quit;

if your database runs on the same system as Dreamweaver, replace
192.168.0.25 with localhost and it should (hopefully) work.

regards
Sascha

On Thu, 2005-10-13 at 16:35 +0200, W Roothman wrote:
> Dear Sascha, Mathias, Blaz
>  
> Thanks, Sacha it worked, the AND localhost='localhost' did it, but my
> new error now is 
>  
> 'localhost not allowed to connect to this MySQL server'
>  
> g...
>  
> R's,
>  
> Will
> - Original Message - 
>     From: Sascha Kaufmann 
> To: php-db@lists.php.net 
> Sent: Thursday, October 13, 2005 9:46 AM
> Subject: Re: [PHP-DB] Re: MySQL Connect using Dreamweaver
> 
> 
> You have to change the password hash to the old 3.23.x format
> if you
> don't want or can't use mysqli. Open a mysql shell and type
> something
> like:
> 
> use mysql;
> UPDATE user SET password = OLD_PASSWORD('yourpass') WHERE user
> =
> 'youruser' AND host = 'localhost';
> 
> On Thu, 2005-10-13 at 08:23 +0200, Blaz wrote:
> > Hi. You must use mysqli which is only supported in php5. Of
> corse if you 
> > will use MySQL5.
> > 
> > regards
> > 
> > W Roothman wrote:
> > > Dear All,
> > > 
> > > I have installed MySQL 5 with PHP 4.3.3.
> > > 
> > > When trying to connect to a database form the 'Database'
> tab in Dreamweaver, I now get the following error when trying
> to select a DB:
> > > 
> > > '1251 Client does not support authentication protocol
> requested by server; consider upgrading MySQL client'
> > > 
> > > Regards,
> > > 
> > > Will
> > 
> 
> -- 
> 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




**

IMPORTANT NOTICE

This communication is for the exclusive use of the intended recipient(s)
named above. If you receive this communication in error, you should
notify the sender by e-mail or by telephone (+44) 191 224 4461, delete
it and destroy any copies of it.

This communication may contain confidential information and material
protected by copyright, design right or other intellectual property
rights which are and shall remain the property of Piranha Studios
Limited. Any form of distribution, copying or other unauthorised use
of this communication or the information in it is strictly prohibited.
Piranha Studios Limited asserts its rights in this communication and
the information in it and reserves the right to take action against
anyone who misuses it or the information in it.

Piranha Studios Limited cannot accept any liability sustained as a
result of software viruses and would recommend that you carry out your
own virus checks before opening any attachment.


<<<>>>AdmID:5AA139178E47E004D7D6F2507D379407



**

IMPORTANT NOTICE

This communication is for the exclusive use of the intended recipient(s)
named above. If you receive this communication in error, you should
notify the sender by e-mail or by telephone (+44) 191 224 4461, delete
it and destroy any copies of it.

This communication may contain confidential information and material
protected by copyright, design right or other intellectual property
rights which are and shall remain the property of Piranha Studios
Limited. Any form of distribution, copying or other unauthorised use
of t

Re: [PHP-DB] Re: MySQL Connect using Dreamweaver

[Sascha Kaufmann]

Will,

is mysql installed on your local machine or on a different server? Do
you work with root or with a newly created user?
If mysql is not installed on your local machine, you have to grant
access from other machines to your database. Here's an example (with a
newly created user).

Example:
your work machine's IP: 192.168.0.25
your username: will

open a mysql shell (with root/superuser privileges) and type:

GRANT ALL PRIVILEGES ON *.* TO 'will'@'192.168.0.25' IDENTIFIED BY
'mysecretpassword';

use mysql;

UPDATE user SET password = OLD_PASSWORD('mysecretpassword') WHERE user =
'will' AND host = '192.168.0.25';

FLUSH PRIVILEGES;

quit;

if your database runs on the same system as Dreamweaver, replace
192.168.0.25 with localhost and it should (hopefully) work.

regards
Sascha

On Thu, 2005-10-13 at 16:35 +0200, W Roothman wrote:
> Dear Sascha, Mathias, Blaz
>  
> Thanks, Sacha it worked, the AND localhost='localhost' did it, but my
> new error now is 
>  
> 'localhost not allowed to connect to this MySQL server'
>  
> g...
>  
> R's,
>  
> Will
> - Original Message - 
>     From: Sascha Kaufmann 
> To: php-db@lists.php.net 
> Sent: Thursday, October 13, 2005 9:46 AM
> Subject: Re: [PHP-DB] Re: MySQL Connect using Dreamweaver
> 
> 
> You have to change the password hash to the old 3.23.x format
> if you
> don't want or can't use mysqli. Open a mysql shell and type
> something
> like:
> 
> use mysql;
> UPDATE user SET password = OLD_PASSWORD('yourpass') WHERE user
> =
> 'youruser' AND host = 'localhost';
> 
> On Thu, 2005-10-13 at 08:23 +0200, Blaz wrote:
> > Hi. You must use mysqli which is only supported in php5. Of
> corse if you 
> > will use MySQL5.
> > 
> > regards
> > 
> > W Roothman wrote:
> > > Dear All,
> > > 
> > > I have installed MySQL 5 with PHP 4.3.3.
> > > 
> > > When trying to connect to a database form the 'Database'
> tab in Dreamweaver, I now get the following error when trying
> to select a DB:
> > > 
> > > '1251 Client does not support authentication protocol
> requested by server; consider upgrading MySQL client'
> > > 
> > > Regards,
> > > 
> > > Will
> > 
> 
> -- 
> 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] Re: MySQL Connect using Dreamweaver

[Sascha Kaufmann]

You have to change the password hash to the old 3.23.x format if you
don't want or can't use mysqli. Open a mysql shell and type something
like:

use mysql;
UPDATE user SET password = OLD_PASSWORD('yourpass') WHERE user =
'youruser' AND host = 'localhost';

On Thu, 2005-10-13 at 08:23 +0200, Blaz wrote:
> Hi. You must use mysqli which is only supported in php5. Of corse if you 
> will use MySQL5.
> 
> regards
> 
> W Roothman wrote:
> > Dear All,
> > 
> > I have installed MySQL 5 with PHP 4.3.3.
> > 
> > When trying to connect to a database form the 'Database' tab in 
> > Dreamweaver, I now get the following error when trying to select a DB:
> > 
> > '1251 Client does not support authentication protocol requested by server; 
> > consider upgrading MySQL client'
> > 
> > Regards,
> > 
> > Will
> 

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

[PHP-DB] Re: MySQL Connect using Dreamweaver

[Blaz]

Hi. You must use mysqli which is only supported in php5. Of corse if you 
will use MySQL5.


regards

W Roothman wrote:

Dear All,

I have installed MySQL 5 with PHP 4.3.3.

When trying to connect to a database form the 'Database' tab in Dreamweaver, I 
now get the following error when trying to select a DB:

'1251 Client does not support authentication protocol requested by server; 
consider upgrading MySQL client'

Regards,

Will


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

[PHP-DB] Re: MySQL/PHP how to put the results of one query in another query

[Basil Mohamed Gohar]

Pedro Quaresma de Almeida wrote:

Hi

I have two MySQL databases, on for aeromodelistas (aeromodelling) and
another for Códigos Postais (Postal Codes). I whant to do the
following query

SELECT CódigoPostal FROM Aeromodelistas 
WHERE CódigoPostal IN 
  (SELECT distinct(CP4) FROM codigopostal.LOCART,codigopostal.DISTRITO
   WHERE codigopostal.LOCART.DD=codigopostal.DISTRITO.DD 
   AND   codigopostal.DISTRITO.DESIG='Coimbra'); 


This query is not working, and I do not know why. If I try the two
queries individualy they work, togheter they don't!?

But the question I want to put to the members of this list is the
following. Is it possible to do the following?

// first do the subquery
$sql_CP4s = "select distinct(CP4) from 
codigopostal.LOCART,codigopostal.DISTRITO where 
codigopostal.LOCART.DD=codigopostal.DISTRITO.DD and 
codigopostal.DISTRITO.DESIG='$nomeDistrito'";


$resultado_CP4s = mysql_query($sql_CP4s,$ligacao);

$linha_CP4s = mysql_fetch_assoc($resultado_CP4s);

// then use it's results in the main query

$sql_Aero_Dist_Masc = "select count(Nome) from Aeromodelistas where 
year(AnoQuota)=2005 and Sexo='Masculino' and Distrito IN $linha_CP4s";


How can we include the results (not just one) of one query in another
query?

Is it possible?

The first question I have is what version of MySQL are you using? 
Subqueries (which is what you're trying to perform) are only supporting 
in MySQL 4.1 and above.


If you do not have MySQL 4.1 or higher installed, you will not be able 
to perform the query as you have it above, but rather, you might 
consider a way of performing the same query as a JOIN.  I didn't inspect 
your query heavily, so I don't know if it's possible or not.


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

[PHP-DB] RE: mySQL UPDATE

[Bastien Koert]

whats the col type of the bible-verse_ref field? If its not a numeric field 
(ie autonumber key) then perhaps consider adding one, and updating on that 
key...


Bastien


From: "Ron Piggott" <[EMAIL PROTECTED]>
Reply-To: "Ron Piggott" <[EMAIL PROTECTED]>
To: "PHP DB" 
CC: "Bastien Koert" <[EMAIL PROTECTED]>
Subject: mySQL UPDATE Date: Thu, 9 Jun 2005 15:00:14 -0400

I created a PHP based mailing list that sends out a Bible verse and a
quotation each day. Today's verses were:


Bible verse of the day:

Matthew 17:19-21 Then the disciples came to Jesus privately and said, "Why
could we not cast it out?"  And He said to them, "Because of the littleness
of your faith; for truly I say to you, if you have faith as a mustard seed,
you shall say to this mountain, 'Move from here to there,' and it shall
move; and nothing shall be impossible to you.  But this kind does not go 
out

except by prayer and fasting." ( NASB )



Quiet Time Quotation And Reflection Topic of the day:

If you hold the gospel up and you become it's shadow do you fit?



In both the Bible verse table and the quotation table is a "current verse 
of
the day" column that the UPDATE command is suppose to change from a 0 to a 
1
so the associated web page will be able to display and a few other 
functions

will know the current verse of the day.  The verses are selected randomly.

Both the UPDATE for the Bible verse and the UPDATE for the quotation 
failed.

I know the code works over all.  From my estimate the use of " or ' is the
reason why the UPDATE didn't work.

Let me show you some code:


It selects a Bible verse:
SELECT * FROM bible ORDER BY RAND() LIMIT 1

Then it puts the text into variables:
$bible_verse_ref=mysql_result($result,$i,"bible_verse_ref");
$bible_verse_text=mysql_result($result,$i,"bible_verse_text");
$bible_verse_translation=mysql_result($result,$i,"bible_verse_translation");

Then immediately following this is the UPDATE that didn't work:
UPDATE `bible` SET `current_verse_of_the_day` = '1' WHERE `bible_verse_ref`
LIKE '$bible_verse_ref' AND `bible_verse_text` LIKE '$bible_verse_text' AND
`bible_verse_translation` LIKE '$bible_verse_translation' AND
`current_verse_of_the_day` LIKE '0' LIMIT 1

Where there are no ' or " this piece of code works just fine.  I am
essentially retrieving a Bible verse from the table and then immediately
searching for it to change the 'current_verse_of_the_day' to change from 0
to 1.  I have manually made the change for today and the
current_verse_of_the_day value was 0.  Similar code is used for the
quotation --- but there is no need for me to repeat it here.

Any ideas how I am able to get the use of ' or " to work on this page?

Ron



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

[PHP-DB] Re: MySQL Newsgroup?

[John Swartzentruber]

On 4/26/2005 2:56 PM Nhan wrote:
I seek for a MySQL newsgroup, can anybody help me?
Martin
If you really want a newsgroup and not the mailing lists, you can get 
them at gmane.org. The general list is gmane.comp.db.mysql.general, but 
there are others as well.

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

[PHP-DB] Re: MySQL Newsgroup?

[PHP-DB]

Go to http://lists.mysql.com/
Noong 02:56 AM 4/27/2005, Ang sabi ni Nhan...
I seek for a MySQL newsgroup, can anybody help me?
Martin
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: MySQL query problems...

[Frank M Flynn]

The error message and  when I have problems I find it's very helpful to 
print  the  actual value of the $query  variable.

In your case it looks like
		   AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname
is adding   AND acct_db.key1 LIKE "%".AllMid_Data.CPU_Hostname
and you can't join two strings in SQL with a '.' like you can in PHP 
you should use a + for the  two strings.  I think what  you want is:

  AND acct_db.key1 LIKE \"%\" + AllMid_Data.CPU_Hostname
Good Luck,
Frank
On Mar 24, 2005, at 1:33 AM, [EMAIL PROTECTED] wrote:
From: "NIPP, SCOTT V \(SBCSI\)" <[EMAIL PROTECTED]>
Date: March 23, 2005 3:18:44 PM PST
To: 
Subject: MySQL query problems...
I am getting errors for the following queries.  This query seems
to work fine in phpMyAdmin.
$query2 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, accounts
   WHERE AllMid_Data.CPU_IN_SVC = \"Y\"
   AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\"
   AND accounts.sbcuid = $sbcuid
   AND accounts.system = AllMid_Data.CPU_Hostname
   AND accounts.ctime IS NULL
   ORDER BY CPU_Hostname ASC";
$results2 = mysql_query($query2, $Prod) or die(mysql_error());
$system2 = mysql_fetch_assoc($results2);
$query3 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, acct_db
   WHERE AllMid_Data.CPU_IN_SVC = \"Y\"
   AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\"
   AND acct_db.key1 LIKE \"$sbcuid%\"
   AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname
   ORDER BY CPU_Hostname ASC";
$results3 = mysql_query($query3, $Prod) or die(mysql_error());
$system3 = mysql_fetch_assoc($results3);
I am assuming that I have an issue with query3.  Thanks in
advance.
Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

[PHP-DB] Re: MySQL Error 1251 / phpMyAdmin

[Michael Stassen]

The short answer is, download the php source and add
  --with-mysql=/your/path/to/mysql
to your build options.  The long answer is in the directions in the php 
manual .

Michael
GH wrote:
I am still new to the PHP scene could you tell me how I "rebuild" the
PHP with the client library...
On Mon, 07 Feb 2005 12:20:26 -0500, Michael Stassen
<[EMAIL PROTECTED]> wrote:
You have mysql 4.1.8, but your php was built with the library for an earlier
version, which, as the error message says, doesn't support the new, more
secure authentication protocol.  Your choices are to tell mysql to use the
older, less-secure protocol, or build a copy of php using your current mysql
client library.  See the manual for more
.
Michael
GH wrote:

I just installed phpMyAdmin 2.6.1 on my Windows Laptop with PHP
Version 4.3.10  [Build Date  Dec 14 2004 17:46:48] and mySql 4.1.8
I am receiving the enclosed error  when I attempt to go into
phpMyAdmin and do not know how to solve the issue...
Any assistance would be greatful.
Thank you
Gary
//## ERROR RECIEVED //
Welcome to phpMyAdmin 2.6.1
phpMyAdmin tried to connect to the MySQL server, and the server
rejected the connection. You should check the host, username and
password in config.inc.php and make sure that they correspond to the
information given by the administrator of the MySQL server.
Error
MySQL said:
#1251 - Client does not support authentication protocol requested by server


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

[PHP-DB] Re: MySQL Error 1251 / phpMyAdmin

[GH]

I am still new to the PHP scene could you tell me how I "rebuild" the
PHP with the client library...


On Mon, 07 Feb 2005 12:20:26 -0500, Michael Stassen
<[EMAIL PROTECTED]> wrote:
> 
> You have mysql 4.1.8, but your php was built with the library for an earlier
> version, which, as the error message says, doesn't support the new, more
> secure authentication protocol.  Your choices are to tell mysql to use the
> older, less-secure protocol, or build a copy of php using your current mysql
> client library.  See the manual for more
> .
> 
> Michael
> 
> GH wrote:
> 
> > I just installed phpMyAdmin 2.6.1 on my Windows Laptop with PHP
> > Version 4.3.10  [Build Date  Dec 14 2004 17:46:48] and mySql 4.1.8
> >
> > I am receiving the enclosed error  when I attempt to go into
> > phpMyAdmin and do not know how to solve the issue...
> >
> > Any assistance would be greatful.
> >
> > Thank you
> > Gary
> >
> >
> > //## ERROR RECIEVED //
> >
> > Welcome to phpMyAdmin 2.6.1
> >
> > phpMyAdmin tried to connect to the MySQL server, and the server
> > rejected the connection. You should check the host, username and
> > password in config.inc.php and make sure that they correspond to the
> > information given by the administrator of the MySQL server.
> >
> > Error
> > MySQL said:
> >
> > #1251 - Client does not support authentication protocol requested by server
> >
>

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

[PHP-DB] Re: MySQL Error 1251 / phpMyAdmin

[Michael Stassen]

You have mysql 4.1.8, but your php was built with the library for an earlier 
version, which, as the error message says, doesn't support the new, more 
secure authentication protocol.  Your choices are to tell mysql to use the 
older, less-secure protocol, or build a copy of php using your current mysql 
client library.  See the manual for more 
.

Michael
GH wrote:
I just installed phpMyAdmin 2.6.1 on my Windows Laptop with PHP
Version 4.3.10  [Build Date  Dec 14 2004 17:46:48] and mySql 4.1.8
I am receiving the enclosed error  when I attempt to go into
phpMyAdmin and do not know how to solve the issue...
Any assistance would be greatful.
Thank you
Gary
//## ERROR RECIEVED //
Welcome to phpMyAdmin 2.6.1
phpMyAdmin tried to connect to the MySQL server, and the server
rejected the connection. You should check the host, username and
password in config.inc.php and make sure that they correspond to the
information given by the administrator of the MySQL server.
Error 
MySQL said:  

#1251 - Client does not support authentication protocol requested by server
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Re: MySQL version issue

[Norland, Martin]

> -Original Message-
> From: Doug Thompson [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, January 05, 2005 8:44 AM
> 
> I isn't my intention to start a peeing contest, but if *you* get rid
of the chip and actually read the manual excerpt, the equivalent SQL
statements it cites are very definitely available in mysql 3.xx.xx.

This will be brief, because this is rolling towards non-productiveness -
but, while you are technically right - it's still not an answer for him.

The new feature us "do this OR (IF CONDITION) that" and the equivalent
statement is "do that" with the precondition of (IF CONDITION).  Of
course the relevant SQL statements are available to independently insert
or update, it'd be pretty useless without.

So, yeah - solution is to bring the logic into the app, or get a new
host.  'nuff said.

Cheers,

- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.


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

Re: [PHP-DB] Re: MySQL version issue

[Doug Thompson]

I isn't my intention to start a peeing contest, but if *you* get rid of the 
chip and actually read the manual excerpt, the equivalent SQL statements it 
cites are very definitely available in mysql 3.xx.xx.
DT
Mark Benson wrote:
Doug, read my e-mail again, specifically the second and third paragraphs about 
what *version* of MySQL my host's server is using, then look at the manual 
entry again (specifically the first line you quoted).
The _mysql manual_ says... "If you specify the ON DUPLICATE KEY UPDATE clause (new 
in MySQL 4.1.0)" thus it only works in 4.1.0 and later, and my host is running MySQL 
3.23.58 (not 3.25.58 as I previously said, not that it makes much odds!). Anyhoo thanks 
for giving it a shot, RBR (read before replying) in future, there's a good chap ;o)
Bastien, thanks sometimes one needs a new perspectiuve on these things, I was 
stuck in my mysql_query brackets. Putting mind to matter using your tips it 
would actually be fairly simple to do that. Again thank you for providing an 
exit() to my stuck loop ;o)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: MySQL version issue

[Mark Benson]

Doug, read my e-mail again, specifically the second and third paragraphs about 
what *version* of MySQL my host's server is using, then look at the manual 
entry again (specifically the first line you quoted).
The _mysql manual_ says... "If you specify the ON DUPLICATE KEY UPDATE clause 
(new in MySQL 4.1.0)" thus it only works in 4.1.0 and later, and my host is 
running MySQL 3.23.58 (not 3.25.58 as I previously said, not that it makes much 
odds!). Anyhoo thanks for giving it a shot, RBR (read before replying) in 
future, there's a good chap ;o)

Bastien, thanks sometimes one needs a new perspectiuve on these things, I was 
stuck in my mysql_query brackets. Putting mind to matter using your tips it 
would actually be fairly simple to do that. Again thank you for providing an 
exit() to my stuck loop ;o)

-- 
Mark Benson

http://homepage.mac.com/markbenson

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

[PHP-DB] Re: mysql to access

[Bogomil Shopov]

Hello
The best decision is to make your own script to migrate database form 
mysql to Acess. You can try this software (working good) 
http://www.handyarchive.com/free/mysql2access/

Regards
Bogomil Shopov
http://purplerain.org
Matthew Perry wrote:
Does anyone know of a FREE program that exports MySQL to access?  The trial
versions of the ones I have downloaded do not export all the records of the
database.
- Matthew 


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

[PHP-DB] Re: MySQL Data Not Returned

[Neil Smith [MVP, Digital media]]

My guess (and it will be correct) is that you've got a not visible 
character on the end of your email address in the database. Such a 
character could be a space, newline, tab or other similar character.

Make sure you use trim($email_address) to create the field to insert into 
the database !

Check the actual string you get returned from the query on the DB with the 
query
SELECT * FROM mailinglist WHERE email like '$email%'

Use strlen($resul["email"]); and you'll see that the length of the email 
address is not the same as the number of characters in the email address if 
you count them manually.

Cheers - Neil
At 11:54 13/11/2004 +, you wrote:
I search the list like so:
$email="[EMAIL PROTECTED]";
$query=mysqli_query($cnn,"SELECT * FROM mailinglist WHERE
email='$email'");
When I do:
Echo mysqli_num_rows($query);
I get 0 back, The address I am searching is in the database, I have
triple checked it with phpmyadmin.
The only way I can get it to return the record I am looking for is to do
this.
$email="[EMAIL PROTECTED]";
$query=mysqli_query($cnn,"SELECT * FROM mailinglist WHERE email like
'$email%'");
Then this finally returns 1. I have tried many different email
addresses; nothing comes back, not even from the SQL query window within
phpmyadmin v. 2.6.0-rc2
This has been puzzling me for hours on end. If anyone can provide any
help, that would be great
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: MySQL backup software

[Frank Flynn]

There must be a dozen solutions here; mysqldump and cron come to mind 
but you haven't been very specific with your needs.

Is your database server  Windows or Unix?  Can you write a simple 
script (or modify an example) to fit your needs?  What are you going to 
back up to?  Another server? Tape?  A different disk on your server?  
The same disk? (bad idea on so many levels).

Why are you backing up?  Disaster recovery?  History? Data integrity?  
How big is the DB(s)?  How often to you intend to back up?  Or if you 
did crash and burn and had to go back to a back up how much data (how 
long) could you tolerate loosing?

It's a kind of interesting discussion and the only wrong answers are 
not to back up and perhaps to back up to the same disk if you want to 
be able to recover.  But if you answer these questions we'll give you 
some options.

Frank
On Oct 27, 2004, at 6:57 AM, "Perry, Matthew (Fire Marshal's Office)" 
<[EMAIL PROTECTED]>t wrote:

From: "Perry, Matthew (Fire Marshal's Office)" 
<[EMAIL PROTECTED]>
Date: October 27, 2004 6:57:57 AM PDT
To: [EMAIL PROTECTED]
Subject: MySQL backup software

Does anyone know a free MySQL backup program to schedule regular 
backups?

[PHP-DB] Re: MySQL backup software

[pete M]

Matthew Perry wrote:
Does anyone know a free MySQL backup program to schedule regular backups?

This is the script I use - its wicked cos it can ftp and email backups 
as well
http://worldcommunity.com/opensource/utilities/mysql_backup.html

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

[PHP-DB] Re: mysql error and resource ID:

[Eric McGrane]

A snippet of the source code would help.

E

"Stuart Felenstein" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> First time setting something like this up. So probably
> making some major mistakes.
> Anyway I get this message : "mysql_error(Resource id
> #2)"
>
> I know it's pointing me to something but not sure what
> .
>
> My suspicion since I'm driving blind :) is that I'm
> attempting an insert using invalid or incorrectly
> stated variables.
>
> Code:
>
> function begin()
> {
> @mysql_query("BEGIN");
> }
> function commit()
> {
> @mysql_query("COMMIT");
> }
> function rollback()
> {
> @mysql_query("ROLLBACK", $link);
> }
> @mysql_connect("myserver","myusername", "mypassword")
> or die(mysql_error());
>
> @mysql_select_db("mydatabase") or die(mysql_error());
> $link = mysql_connect("myserver", "myusername",
> "mypassword");
>
> $query = "INSERT INTO MyTable (RecordID,UserID,Field3,
> Field4, ., )
> values
> // these are all the session variables that I'm trying
> // to use to insert value , is that wrong
>
>
(null,null,$f1a,$f2a,$f2c,$f2d,$f2e,$f2g,$f5b,$f3m,$f3n,$f3e,$f3f,$f3g,$f3h,
$f3i,$f3j,
> $f3k,$f3l)";
> begin(); // transaction begins
> $result = @mysql_query($query, $link);
>
> if(!$result)
> {
> rollback(); // transaction rolls back
> echo "you rolled back";
> echo " . mysql_error($link)";
> exit;
> }
> else
> {
> commit(); // transaction is committed
> echo "your insertion was successful";
> }
> ?>

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

[PHP-DB] Re: MySQL denying access to everything part 2 [CANCELED]

[AceZero2790]

Sorry, then. Just that people were helping me earlier...but anyway never mind 
thanks for all your previous help.

Re: [PHP-DB] Re: MySQL denying access to everything

[Jason Wong]

On Wednesday 18 August 2004 22:30, [EMAIL PROTECTED] wrote:

> Look I'm totally new to this, so all this MySQL stuff you are sending me
> too is like an alien language to me.

New doesn't excuse you from reading documentation. Last time I looked the 
documentation was available in English too.

> Can you just tell me how to delete all this MySQL stuff so I can start
> again or something? Because even when I reinstall MySQL and delete my old
> MySQLgui.exe, it still gives me the same problem...

All the instructions for installation and configuration are in the MySQL 
manual. Uninstalling would depend on how you installed it in the first place.

And in any case this isn't the list for discussing how to install and 
configure MySQL.

-- 
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-db
--
/*
Nature abhors a virgin -- a frozen asset.
-- Clare Booth Luce
*/

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

Re: [PHP-DB] Re: MySQL denying access to everything

[randy]

you need to ditch the 'data' directory (in the MySQL installation
root) and start from scratch. If you just re-install the MySQL server,
I don't think it will overwrite your data directory.


On Wed, 18 Aug 2004 10:30:25 EDT, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> Look I'm totally new to this, so all this MySQL stuff you are sending me too
> is like an alien language to me.
> 
> Can you just tell me how to delete all this MySQL stuff so I can start again
> or something? Because even when I reinstall MySQL and delete my old
> MySQLgui.exe, it still gives me the same problem...
> 


-- 
randy [EMAIL PROTECTED]

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

[PHP-DB] Re: MySQL denying access to everything

[AceZero2790]

Look I'm totally new to this, so all this MySQL stuff you are sending me too 
is like an alien language to me.

Can you just tell me how to delete all this MySQL stuff so I can start again 
or something? Because even when I reinstall MySQL and delete my old 
MySQLgui.exe, it still gives me the same problem...

[PHP-DB] RE: MySQL '!=' ???

[Jensen, Kimberlee]

Whenever you are trying to exclude a list, you should use AND, not OR. This is a logic 
issue. However, to make it more exact, streamlined and simpler use the NOT IN clause.

SELECT ... FROM ... WHERE country NOT IN ('Argentina', 'USA', 'Mexico', etc.)



-Original Message-
From:   [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent:   Tue 7/20/2004 4:40 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:[BULK] - php-db Digest 20 Jul 2004 23:40:15 - Issue 2503

php-db Digest 20 Jul 2004 23:40:15 - Issue 2503

Topics (messages 35403 through 35420):

Re: howto get PK id after INSERT??
35403 by: Vincent Jordan
35404 by: Jason Wong
35405 by: jeffrey_n_Dyke.Keane.com
35410 by: John W. Holmes

MySQL '!=' ???
35406 by: Tristan.Pretty.risk.sungard.com
35407 by: Hutchins, Richard
35408 by: VANDOORINE A RsrhCppRfaRva

Re: Wait Statement... ?
35409 by: John W. Holmes
35413 by: Daevid Vincent
35414 by: John W. Holmes
35415 by: Ignatius Reilly
35416 by: Pablo M. Rivas
35417 by: Tim Van Wassenhove
35418 by: Jason Wong
35420 by: John W. Holmes

Re: DB table creation question
35411 by: Swan, Nicole

pg_end_copy error using PHP with PostgreSQL
35412 by: Robert Fitzpatrick

Concurrent rutines
35419 by: Luis Morales

Administrivia:

To subscribe to the digest, e-mail:
[EMAIL PROTECTED]

To unsubscribe from the digest, e-mail:
[EMAIL PROTECTED]

To post to the list, e-mail:
[EMAIL PROTECTED]


--

Re: [PHP-DB] Re: Mysql and rollbacks

[John W. Holmes]

Peter Westergaard wrote:
... or, am I barking up the wrong tree with Mysql, and should I change
database platforms?  (You'll never convince me to give up PHP though.
muahaha. Except for sql-level stored procedures where necessary, that is).
Maybe? :)
PostgreSQL and Firebird are two other open source free databases that 
have transaction support and stored procedures, IIRC. Check them out. If 
you run your own server, it's easy to get these. You may have trouble 
finding a hosting company consistently offering them, though.

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Re: Mysql and rollbacks

[Hutchins, Richard]

If memory serves me correctly, a couple of the PEAR database abstraction
classes support rollback functionality. Of course, YMMV.

Rich

> -Original Message-
> From: Peter Westergaard [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 13, 2004 11:24 AM
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] Re: Mysql and rollbacks
> 
> 
> ... or, am I barking up the wrong tree with Mysql, and should I change
> database platforms?  (You'll never convince me to give up PHP though.
> muahaha. Except for sql-level stored procedures where 
> necessary, that is).
> 
> -P
> 
> -- 
> 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] Re: Mysql and rollbacks

[Peter Westergaard]

... or, am I barking up the wrong tree with Mysql, and should I change
database platforms?  (You'll never convince me to give up PHP though.
muahaha. Except for sql-level stored procedures where necessary, that is).

-P

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

[PHP-DB] Re: MySQL query, using DISTINCT...

[Rui Cunha]

i don't know if someone already gave you a suggestion such as: 

select distinct mail
from (
select mail , filename,  count(*)
from table
having count(*) > 1
group by mail,filename
); 

i presume that you're looking for disctinct mails of users that downloaded 
the same file more than once. 

hope it helps 

Rui Cunha 

[EMAIL PROTECTED] writes: 

I have a system that tracks downloads, capturing loadsa info, but of 
interest here, is email and filename.
Simply put, I wanna show all results where file name AND email are unique.
(so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, 
I want to only see it once.) 

What am I doing wrong...? 

SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu`  = 
 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' 

Tris... 

*
The information contained in this e-mail message is intended only for 
the personal and confidential use of the recipient(s) named above.  
If the reader of this message is not the intended recipient or an agent
responsible for delivering it to the intended recipient, you are hereby 
notified that you have received this document in error and that any
review, dissemination, distribution, or copying of this message is 
strictly prohibited. If you have received this communication in error, 
please notify us immediately by e-mail, and delete the original message.
*** 

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

Re[2]: [PHP-DB] Re: MySQL query, using DISTINCT...

[Pablo M. Rivas]

Hello Tristan,
 Select count(id) as howmany, file_name, email from  `completed_downloads`
 where bu='reech' and date  BETWEEN '2004-06-01' AND '2004-06-30'
 group by file_name, email

 

>> > I have a system that tracks downloads, capturing loadsa info, but of
>> > interest here, is email and filename.
>> > Simply put, I wanna show all results where file name AND email are 
TPrsc> unique.
>> > (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a 
TPrsc> table,
>> > I want to only see it once.)
>> >
>> > What am I doing wrong...?
>> >
>> > SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE 
TPrsc> `bu`  =
>> >  'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30'
>> >
>> > Tris...



-- 
Best regards,
 Pablo

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

Re: [PHP-DB] Re: MySQL query, using DISTINCT...

[zareef ahmed]

Hi,

 using "group by" can also be usefull.

zareef ahmed  

--- Torsten Roehr <[EMAIL PROTECTED]> wrote:
> "Tristan Pretty" <[EMAIL PROTECTED]>
> wrote in message
>
news:[EMAIL PROTECTED]
> com...
> > I have a system that tracks downloads, capturing
> loadsa info, but of
> > interest here, is email and filename.
> > Simply put, I wanna show all results where file
> name AND email are unique.
> > (so if email '[EMAIL PROTECTED]' has filename
> 'word.doc' 5 times in a table,
> > I want to only see it once.)
> >
> > What am I doing wrong...?
> >
> > SELECT DISTINCT(file_name, email) FROM
> `completed_downloads` WHERE `bu`  =
> >  'reech' AND date BETWEEN '2004-06-01' AND
> '2004-06-30'
> >
> > Tris...
> 
> Hi Tristan,
> 
> I'm not an MySQL expert but DISTINCT removes
> duplicate *rows* from your
> result set. As you select email AND file_name you
> will always have a unique
> row (combination of email and file_name). Try
> without specifying file_name:
> 
> SELECT DISTINCT email FROM `completed_downloads`
> WHERE `bu` = 'reech' AND
> date BETWEEN '2004-06-01' AND '2004-06-30'
> 
> Regards, Torsten Roehr
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 


=
Zareef Ahmed :: A PHP Developer in Delhi(India).
Homepage :: http://www.zasaifi.com



__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

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

Re: [PHP-DB] Re: MySQL query, using DISTINCT...

[Tristan . Pretty]

> > I have a system that tracks downloads, capturing loadsa info, but of
> > interest here, is email and filename.
> > Simply put, I wanna show all results where file name AND email are 
unique.
> > (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a 
table,
> > I want to only see it once.)
> >
> > What am I doing wrong...?
> >
> > SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE 
`bu`  =
> >  'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30'
> >
> > Tris...
> 
> Hi Tristan,
> 
> I'm not an MySQL expert but DISTINCT removes duplicate *rows* from your
> result set. As you select email AND file_name you will always have a 
unique
> row (combination of email and file_name). Try without specifying 
file_name:
> 
> SELECT DISTINCT email FROM `completed_downloads` WHERE `bu` = 'reech' 
AND
> date BETWEEN '2004-06-01' AND '2004-06-30'
> 
> Regards, Torsten Roehr

Ah...
But if the user has downloaded 3 files, I need to know that.
distinct email alone, wouldn't pick that up...
Cheers for your help though, I'm getting there...!
Any other ideas?

*
The information contained in this e-mail message is intended only for 
the personal and confidential use of the recipient(s) named above.  
If the reader of this message is not the intended recipient or an agent
responsible for delivering it to the intended recipient, you are hereby 
notified that you have received this document in error and that any
review, dissemination, distribution, or copying of this message is 
strictly prohibited. If you have received this communication in error, 
please notify us immediately by e-mail, and delete the original message.
***

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

[PHP-DB] Re: MySQL query, using DISTINCT...

[Torsten Roehr]

"Tristan Pretty" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
com...
> I have a system that tracks downloads, capturing loadsa info, but of
> interest here, is email and filename.
> Simply put, I wanna show all results where file name AND email are unique.
> (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table,
> I want to only see it once.)
>
> What am I doing wrong...?
>
> SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu`  =
>  'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30'
>
> Tris...

Hi Tristan,

I'm not an MySQL expert but DISTINCT removes duplicate *rows* from your
result set. As you select email AND file_name you will always have a unique
row (combination of email and file_name). Try without specifying file_name:

SELECT DISTINCT email FROM `completed_downloads` WHERE `bu` = 'reech' AND
date BETWEEN '2004-06-01' AND '2004-06-30'

Regards, Torsten Roehr

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

[PHP-DB] Re: MySQL: Random select with specific count of a column

[franciccio]

I suggest to first analize the problem and then go through the query, php
coding  ...etc etc

In the table u have only one superkey which is also a key and it is made of
the three fields (attributes) 'category', 'language' and ' name'. You should
consider to look for a prymary key randomly, toghether with some restriction
in the search. Something like select the pryimary key from the table where
'category' equals a number(1, 2,3,n) and 'name' equals some random
criterium generated number. A random criteria could be generating a random
variables between the ascii code representing the alphabet letters (from 97
to 122 to cover a,b,c,...z).
You need to do some work before u can get an executable code here.
Hope can open a road
Bye


"Torsten Roehr" <[EMAIL PROTECTED]> ha scritto nel messaggio
news:[EMAIL PROTECTED]
> Hi,
>
> I've got the following table:
>
> categorylanguagename
> 1   de  a
> 1   de  b
> 1   de  c
> 2   de  a
> 2   de  b
> 2   de  c
> 3   de  a
> 3   de  b
> 3   de  c
> ...
> 1   en  a
> 1   en  b
> 1   en  c
>
> I would like to select 6 random rows where the language is 'de' AND make
> sure that I will always have 2 rows of EACH category in my result set:
> 1   de  b
> 1   de  c
> 2   de  a
> 2   de  c
> 3   de  a
> 3   de  b
>
> Any help greatly appreciated.
>
>
> Thanks and best regards,
>
> Torsten Roehr

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

Re: [PHP-DB] Re: MySQL executes several times the same request

[Randy C Boland]

I'm not sure where the value of $counter is coming from, but it you're
getting it from a SQL SELECT query, it would be more efficient to just
use:
UPDATE statistics SET VALUE(`counter` = `counter`+1)

It's just one less query on the DB, which could make a difference if
you're getting heavy traffic.

> > > $counter++;
> > > $SQL = "UPDATE statistics SET VALUE (`counter` = $counter)"
> > > $res = mysql_query($SQL);
> > > mysql_closedb($DB);

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

[PHP-DB] Re: MySQL: Random select with specific count of a column

[Torsten Roehr]

"Rui Cunha" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi Torsten,
>
> until now,the best i could get was getting 6 random rows...still working
on
> ensuring to retrive just 2 rows for category...meanwhile,you can solve the
> problem easily with a php loop trough the following query:
>
> select field_id , language , category
> from yourtable
> where language = 'de'
> group by category , field_id
> order by category , rand()
> limit 6;
>
> PS1: i'm considering you're using just one table and my table structure is
> as follows: yourtable (field_id , language, category).
> in case you're using more than 1 table, just add the required joins...
>
> hope this helps.

Hi Rui,

thank you very much for your efforts. I guess I have to do seperate queries
(as you suggested) for each category and then join the results.

Thanks again and best regards,

Torsten

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

[PHP-DB] Re: MySQL: Random select with specific count of a column

[Rui Cunha]

Hi Torsten, 

until now,the best i could get was getting 6 random rows...still working on 
ensuring to retrive just 2 rows for category...meanwhile,you can solve the 
problem easily with a php loop trough the following query: 

select field_id , language , category
from yourtable
where language = 'de'
group by category , field_id
order by category , rand()
limit 6; 

PS1: i'm considering you're using just one table and my table structure is 
as follows: yourtable (field_id , language, category).
in case you're using more than 1 table, just add the required joins... 

hope this helps. 

Rui Cunha 


Torsten Roehr writes: 

"Pablo M. Rivas" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Hello Torsten, 

 This is one, but I think you can find another one:
 Supose your table is called tablename 

 select * from tablename as a left join tablename as b on
 a.category = b.category and a.name<>b.name and
 a.language=b.language where a.language='de' and b.name is not
 null group by a.category
Hi Pablo, 

thanks for your help. Unfortunately it's always returning the same row for
each category (maybe because of the group by) and only one row for each
category. I need to select 2 random rows for each category. Any more ideas? 

Thanks, Torsten 

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

[PHP-DB] Re: MySQL executes several times the same request

[PHPDiscuss - PHP Newsgroups and mailing lists]

Justin Patrin wrote:

> Loïc moisy wrote:

> > Hi Everyone !
> > 
> > I've coded a php site for a musical store. At the end of every page, before
> > closing the connection, I send an UPDATE request to increment a statistical
> > tool. Recently I saw weird results, so I tried to understand. Looking in my
> > logs, I saw that this request could be sent several times, arbitrary - can
> > be 5 times or 8 or whatever... So my counter is incremented from 4 to 8
> > times. The server seems to resend the whole requestes several times. I have
> > read all of my scripts, even coded some trace code, and sure there is no
> > loop.
> > 
> > The code seems like that:
> > 
> > $counter++;
> > $SQL = "UPDATE statistics SET VALUE (`counter` = $counter)"
> > $res = mysql_query($SQL);
> > mysql_closedb($DB);
> > 
> > So, I do not see anything strange. Can u help me ? My own thought about it
> > is something like a buffer not empty or something like that.
> > 
> > Thanks !

> We had a problem like this on our site for a while. It turned out that 
> soneone had put an empty image tag on the site which was causing the 
> browser to go to the same page *again*. Check to make sure that all of 
> your images have the src attribute filled out.


Thanks for your answer ! I am just back now because I think the solution
can help someone. In my case, in fact, your answer let me work around and
look if something could occurs in this special case but not. In fact, the
problem come from the browser, but the several calls to the server is not
coming from an empty src tag, but from a golive script: the template of
the site use them, and one preload several pages I think, and so the
counter is increased several times...
Many thanks.

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

[PHP-DB] Re: mysql and indexes

[Kim Steinhaug]

use the EXPLAIN infront of your select query to see what indexes are in use.
If you have a double index it wont hurt you, except that you are wasting
twice
as much space in your DB. This would also make your DB slower since the
database
needs to index both indexes all the time.

Duplicate indexes should be avoided since there are no reason to have them.

--
--
Kim Steinhaug
--
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
--
www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
--

"Michael Gale" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
>I know this question is more mysql related then PHP with mysql .. but
> 
>
> If I do a "SHOW INDEX from table_name"
>
> I see what appears to be two index for the same column ?
>
> table_name 0 PRIMARY 1 columnname A NULL NULL NULL
> table_name 0 columnname1 columnname A 6623 NULL NULL
>
> Could the multiple index cause a problem ? I am trying to speed up a DB
> query ?
>
>
> Michael.
>

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

[PHP-DB] Re: MySQL executes several times the same request

[Justin Patrin]

Loïc moisy wrote:
Hi Everyone !
I've coded a php site for a musical store. At the end of every page, before
closing the connection, I send an UPDATE request to increment a statistical
tool. Recently I saw weird results, so I tried to understand. Looking in my
logs, I saw that this request could be sent several times, arbitrary - can
be 5 times or 8 or whatever... So my counter is incremented from 4 to 8
times. The server seems to resend the whole requestes several times. I have
read all of my scripts, even coded some trace code, and sure there is no
loop.
The code seems like that:
$counter++;
$SQL = "UPDATE statistics SET VALUE (`counter` = $counter)"
$res = mysql_query($SQL);
mysql_closedb($DB);
So, I do not see anything strange. Can u help me ? My own thought about it
is something like a buffer not empty or something like that.
Thanks !
We had a problem like this on our site for a while. It turned out that 
soneone had put an empty image tag on the site which was causing the 
browser to go to the same page *again*. Check to make sure that all of 
your images have the src attribute filled out.

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

[PHP-DB] Re: Mysql not receiving the data

[Rui Cunha]

take a look at the sql insert sintaxe...
it's supposed to be: INSERT INTO table VALUES (val1,val2,...,valn);
all string and date values go between ' '...numeric values don't need 
them...
here's an example: INSERT INTO table VALUES ('abc',500,'12-12-2003'); 

if your table has 5 fields but you don't want to insert values for them all 
then you must specify the fields that you want to insert: 

INSERT INTO table (field1,field2) VALUES (val1,val2); 

hope this helps. 

Rui Cunha 

Andrew Rothwell writes: 

Hi Larry, Thank you very much for the very quick response, I set my php.ini
file (located /etc/php.ini ) for the register_globals = On (it was off by
default) 

Now however I get an error 
Error adding entry: You have an error in your SQL syntax near 's spanish
driver is found shot dead, Inspector Jacques Clouseau is the first off' at
line 8 

My Database is a movie database of my dvd's that I own (for insurance
reasons) 

My addmovie.php is this

  mysql_connect("localhost","username","password");
  mysql_select_db("movies");
  $add = "INSERT INTO movies SET
 movie_name='$movie_name',
 genre='$genre',
 director='$director',
 star1='$star1',
 star2='$star2',
 star3='$star3',
 brief_synopsis='$brief_synopsis',
 imdb_link='$imdb_link'";
  if (@mysql_query($add))
{
  echo("Your entry has been added. 
  $movie_name");
}
  else
{
echo("Error adding entry: " .
mysql_error() . "");
   }
?> 

And the addmovie.htm page (atleast the form action is this) 



  
bordercolordark="#FF0033" bordercolorlight="#66">

  Movie Name 
   

  
 

Andrew 

-Original Message-
From: Larry E. Ullman [mailto:[EMAIL PROTECTED] 
Sent: Sunday, June 13, 2004 11:22 AM
To: Andrew Rothwell
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Mysql not receiving the data 

Online I could see everything, and the pages gave the appearance of 
working, however when I went into the DB using PHPMYADMIN to check the 
status of the new data entered, all I found was blank rows ( for the 
new data since the rebuild, all the old data was there) There were the 
correct number of new rows for the amount of records that I had 
entered, which tells me (unless I am nistaken) that the PHP is talking 
to the DB, and is atleast sending a insert command, but the rest of 
the data is not getting in. -
Without seeing any code whatsoever and since this worked before but no
longer works on a new install, I can only assume that your code was written
with the assumption that register_globals was turned on and it's not on in
your current configuration. 

If that is the case, see the PHP manual or search the Web for the solution
($_POST, $_GET, etc.). 

Larry 

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

[PHP-DB] Re: mysql results, arrays, and for loops

[franciccio]

Hi, here is my solution (one of the possible) it is tested so it should work
fine:

\n";

$record= mysql_fetch_array($recordset, MYSQL_ASSOC);
foreach ($record as $k =>$val)
{
echo "$k\n"; // print the field name in the first row of
the table
for ($i=0;$i<$num_righe;$i++)
{
echo "";
mysql_data_seek($recordset,$i);
$record = mysql_fetch_array($recordset, MYSQL_ASSOC);
foreach ($record as $val){
echo "$val\n"; // print the result of query, starting under
1st row
}
}
mysql_free_result($recordset);
mysql_close();
echo "";
?>
Let me know if it fits your problem.
Best regards
Francesco Basile

"Philip Thompson" <[EMAIL PROTECTED]> ha scritto nel messaggio
news:[EMAIL PROTECTED]
> Hi all!
>
> I am using a select statement to obtain all the dates whenever someone
> submitted a problem in a database. Well, I want to get the result
> (which could be multiple dates) and then print that in a table format
> with some other information on a webpage. So I want to use a FOR loops
> and go through each of the dates and dynamically create a table.
>
> My question is: how do I store the results of the select query? Would I
> want to store them in an array, and then just parse through each
> element of the array, and what is the syntax for that? Or is there a
> better way?
>
> Thanks,
> ~Philip

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

[PHP-DB] Re: mysql results, arrays, and for loops

[Rui Cunha]

hope this code will help you 

 



  $db = mysql_connect ('localhost') or die ($MYSQL_NO_CON);
  mysql_select_db ('database_name',$db) or die ($MYSQL_NO_BD); 

		$query = "SELECT * FROM table" or die ($MYSQL_NO_QUERY);
		$result = mysql_query($query); 

		if (mysql_num_rows($result) > 0 ) { 

			$num_rows = mysql_num_rows($result);
	
			for ($i=0; $i <$num_rows; $i++) {
		
$row = mysql_fetch_row($result); 

echo "$row[0];
			} 

		}else echo "no records found!"; 

?> 

 

Rui Cunha 

Philip Thompson writes: 

Hi all! 

I am using a select statement to obtain all the dates whenever someone 
submitted a problem in a database. Well, I want to get the result (which 
could be multiple dates) and then print that in a table format with some 
other information on a webpage. So I want to use a FOR loops and go 
through each of the dates and dynamically create a table. 

My question is: how do I store the results of the select query? Would I 
want to store them in an array, and then just parse through each element 
of the array, and what is the syntax for that? Or is there a better way? 

Thanks,
~Philip 


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

Re: [PHP-DB] Re: mysql results, arrays, and for loops

[Philip Thompson]

Yes, thanks to all that assisted. I got it working like a charm!
~Philip
On Jun 11, 2004, at 9:30 AM, Torsten Roehr wrote:
"Philip Thompson" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Hi all!
I am using a select statement to obtain all the dates whenever someone
submitted a problem in a database. Well, I want to get the result
(which could be multiple dates) and then print that in a table format
with some other information on a webpage. So I want to use a FOR loops
and go through each of the dates and dynamically create a table.
My question is: how do I store the results of the select query? Would 
I
want to store them in an array, and then just parse through each
element of the array, and what is the syntax for that? Or is there a
better way?
You can loop through your query results in this way:
$result = mysql_query('SELECT * FROM table');
while ($row = mysql_fetch_assoc($result)) {
// row is now the current record set as an associative array, keys
represent the field names
// do what you want with row, e.g. print a field value from column 
date
echo $row['date'];
}

Is this what you are looking for?
Regards,
Torsten Roehr
--
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] Re: mysql results, arrays, and for loops

[Torsten Roehr]

"Philip Thompson" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi all!
>
> I am using a select statement to obtain all the dates whenever someone
> submitted a problem in a database. Well, I want to get the result
> (which could be multiple dates) and then print that in a table format
> with some other information on a webpage. So I want to use a FOR loops
> and go through each of the dates and dynamically create a table.
>
> My question is: how do I store the results of the select query? Would I
> want to store them in an array, and then just parse through each
> element of the array, and what is the syntax for that? Or is there a
> better way?

You can loop through your query results in this way:

$result = mysql_query('SELECT * FROM table');

while ($row = mysql_fetch_assoc($result)) {

// row is now the current record set as an associative array, keys
represent the field names
// do what you want with row, e.g. print a field value from column date
echo $row['date'];
}

Is this what you are looking for?

Regards,
Torsten Roehr

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

[PHP-DB] Re: mysql adding

[Hans Lellelid]

Steven Morgan wrote:
is there anyway to add 1 to an int with 1 mysql query, i know i can pull 
the value down with a SELECT then add 1 with php.. then do an UPDATE on 
it.. but i didn't know if there was any other way?
Do you mean this:
UPDATE tablename SET intcol = intcol + 1
?
Hans
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: MySQL to DBF

[Torsten Roehr]

"Emilio Alvarado" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I need to make a dbf file with some mysql records
> The creation of the dbf with the dbase_create works FINE
> Now I need to fill the dbf, I have a mysql database with some of the data,
I
> have the apropiate SQL to view that data but I need to make an array with
> that data and some "", 0.
> How can i do this
>
> Thanks

$result = mysql_query('SELECT ...');

// open your DBF
$dbf = dbase_open($filename, 2);

// get each row as an array
while ($row = mysql_fetch_row($result)) {
// now insert the row into your DBF, $dbf is your identifier from the
open command
db_add_record($dbf, $row);
}

Haven't tested it, though. Take a look at the manual page. There are some
user comments that might help you:
http://de.php.net/manual/en/function.dbase-add-record.php


Regards, Torsten

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

[PHP-DB] Re: mysql - SELECT DISTINCT problem

[Mookstah]

<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
> I use "SELECT DISTINCT tip, marca, model FROM modele" to select
> records without duplicates on the field "tip". This works ok, BUT if I
> use "SELECT DISTINCT tip, marca, model, id FROM..." (id is
> auto_increment and is the table's primary key) my query won't produce
> any result. ANyone has a clue? Thank you!

DISTINCT is ment for the total ressult, not for 1 field
maybe try "GROUP BY tip" for a better result.


>
> Best regards,
> Marius Panaitescu

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

Re: [PHP-DB] Re: mysql foreign keys

[Uzi Klein]

> Hi.
>
> Any body got an idea how to use hte foreign keys in mysql innodb tables
> and how whould the change affect the performance of the server?
>
> lets say i have Items table, Clients table, and Sales table
> the Sales table always stores ClientID and ItemID
> but not every client/item is there
> a sale can be deleted without harming the client
> nor the item.
>
> i guess (not sure at all) it should go like :
>
> ALTER TABLE Sales
>  ADD FOREIGN KEY ClientID(ClientID)
>  REFERENCES Clients(ClientID)
>  ON DELETE RESTRICT;
>
> ALTER TABLE Sales
>  ADD FOREIGN KEY ItemID(ItemID)
>  REFERENCES Items(ItemID)
>  ON DELETE RESTRICT;
>
> Am I correct?
>
> thanks.

Take a look here:
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

Regards,
Torsten Roehr

I did the test's and it seems ok, i just want a second opinion,
cause of my lack in experience.
I just want to be sure that's the right and best way of doing it.
btw, the indexes are already set for select joins etc.

Thanks

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

[PHP-DB] Re: mysql foreign key syntax / tutorial

[Torsten Roehr]

"Uzi Klein" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi.
>
> Any body got an idea how to use hte foreign keys in mysql innodb tables
> and how whould the change affect the performance of the server?
>
> lets say i have Items table, Clients table, and Sales table
> the Sales table always stores ClientID and ItemID
> but not every client/item is there
> a sale can be deleted without harming the client
> nor the item.
>
> i guess (not sure at all) it should go like :
>
> ALTER TABLE Sales
>  ADD FOREIGN KEY ClientID(ClientID)
>  REFERENCES Clients(ClientID)
>  ON DELETE RESTRICT;
>
> ALTER TABLE Sales
>  ADD FOREIGN KEY ItemID(ItemID)
>  REFERENCES Items(ItemID)
>  ON DELETE RESTRICT;
>
> Am I correct?
>
> thanks.

Take a look here:
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

Regards,
Torsten Roehr

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