subject:"Re\: \[PHP\-DB\] HELP\!"


This will give you:

ERROR : Invalid use of group function

It seems like the use of an aggregate (or how is it spelled?) function 
is not allowed in a join statement...


/Krister

Jon L. wrote:


You can try adding a quick test to the ON statement...

SELECT * FROM TableA
INNER JOIN TableB
  ON TableA.record_id = TableB.record_id
AND TableB.timestamp = MAX(TableB.timestamp)


Now, I haven't tested it.
I can only say the theory of it is accurate.

- Jon L.

On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey <[EMAIL PROTECTED]>
wrote:


I can't see how to accomplish what I need so if anyone has any
suggestions they would be gratefully received...

I'm using mysql 4.0.20 by the way.

I have two tables :

TableA
record_id
product_ref

TableB
timestamp
record_id
action

I want to create a SELECT that joins these 2 tables where the JOIN to
TableB only returns the most recent entry by timestamp.

At present (using PHP) I do a SELECT on TableA then for each record
returned I perform a 2nd SELECT something like :

"SELECT timestamp, action FROM TableB WHERE record_id = '$record_id'
ORDER BY timestamp DESC LIMIT 1"

I now want to do it with one query to enable sorting the results by
'action' from TableB.

Any suggestions?

Hopefully I've made sense, if not I'll happily try and explain further
on request.

--
Graham

--
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] Help with JOIN query

2008-03-06 Thread Jon L.

You can try adding a quick test to the ON statement...

SELECT * FROM TableA
INNER JOIN TableB
  ON TableA.record_id = TableB.record_id
AND TableB.timestamp = MAX(TableB.timestamp)


Now, I haven't tested it.
I can only say the theory of it is accurate.

- Jon L.

On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey <[EMAIL PROTECTED]>
wrote:

> I can't see how to accomplish what I need so if anyone has any
> suggestions they would be gratefully received...
>
> I'm using mysql 4.0.20 by the way.
>
> I have two tables :
>
> TableA
> record_id
> product_ref
>
> TableB
> timestamp
> record_id
> action
>
> I want to create a SELECT that joins these 2 tables where the JOIN to
> TableB only returns the most recent entry by timestamp.
>
> At present (using PHP) I do a SELECT on TableA then for each record
> returned I perform a 2nd SELECT something like :
>
> "SELECT timestamp, action FROM TableB WHERE record_id = '$record_id'
> ORDER BY timestamp DESC LIMIT 1"
>
> I now want to do it with one query to enable sorting the results by
> 'action' from TableB.
>
> Any suggestions?
>
> Hopefully I've made sense, if not I'll happily try and explain further
> on request.
>
> --
> Graham
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] Help with JOIN query


Hi again!

We're getting a bit of topic here, since this is pure SQL.. But anyway...

I've played around with this one a bit since it seemed quite 
interesting... The best I can do is to get the oldest action...


select TableA.record_id, product_ref, action, time_stamp from TableA 
join TableB on TableA.record_id = TableB.record_id group by record_id;


Here's the test data:

mysql> select TableA.record_id, product_ref, action, time_stamp from 
TableA join TableB on TableA.record_id = TableB.record_id;

+---+-+++
| record_id | product_ref | action | time_stamp |
+---+-+++
| 1 | 100 | A  | 20080306220037 |
| 1 | 100 | C  | 20080306220041 |
| 1 | 100 | E  | 20080306220045 |
| 2 | 102 | A  | 20080306220052 |
| 3 | 110 | A  | 20080306220055 |
| 3 | 110 | E  | 20080306220058 |
| 4 | 120 | B  | 20080306220105 |
| 4 | 120 | C  | 20080306220109 |
+---+-+++

And with the query above we get the opposite of the desired behavior, 
the oldest action (if that's the order in the database):


mysql> select TableA.record_id, product_ref, action, time_stamp from 
TableA join TableB on TableA.record_id = TableB.record_id

group by record_id;
+---+-+++
| record_id | product_ref | action | time_stamp |
+---+-+++
| 1 | 100 | A  | 20080306220037 |
| 2 | 102 | A  | 20080306220052 |
| 3 | 110 | A  | 20080306220055 |
| 4 | 120 | B  | 20080306220105 |
+---+-+++
4 rows in set (0.00 sec)

Now is the question: Does anyone know how to get the 'group by' clause 
to leave a specific row 'visible' at top? Like the last inserted or by 
the order of another column...


Since MySQL 4.1 there are also a GROUP_CONCAT() function that can 
concatenate multiple 'rows' to a string in a desired order, but it does 
not support the limit statement... so that won't help us much I think. 
We can get all the actions in a string with the newest first, but then 
some post-stripping of the data is needed.


It seems like you need to do this with two queries in PHP, if no one has 
an answer to the question stated above. You can always buffer your 
result in an array in PHP and do whatever sorting you want to before 
using your data...


With the MAX() function we can found out when the last action was made, 
but we get the wrong action with the correct time:


mysql> select TableA.record_id, product_ref, action, max(time_stamp) 
from TableA join TableB on TableA.record_id = TableB.record_id

group by record_id;
+---+-++-+
| record_id | product_ref | action | max(time_stamp) |
+---+-++-+
| 1 | 100 | A  |  20080306220045 |
| 2 | 102 | A  |  20080306220052 |
| 3 | 110 | A  |  20080306220058 |
| 4 | 120 | B  |  20080306220109 |
+---+-++-+
4 rows in set (0.00 sec)

Hmm... Now I'm stuck! :)

Greetings,
Krister Karlström, Helsinki, Finland

Graham Cossey wrote:


I can't see how to accomplish what I need so if anyone has any
suggestions they would be gratefully received...

I'm using mysql 4.0.20 by the way.

I have two tables :

TableA
record_id
product_ref

TableB
timestamp
record_id
action

I want to create a SELECT that joins these 2 tables where the JOIN to
TableB only returns the most recent entry by timestamp.

At present (using PHP) I do a SELECT on TableA then for each record
returned I perform a 2nd SELECT something like :

"SELECT timestamp, action FROM TableB WHERE record_id = '$record_id'
ORDER BY timestamp DESC LIMIT 1"

I now want to do it with one query to enable sorting the results by
'action' from TableB.

Any suggestions?

Hopefully I've made sense, if not I'll happily try and explain further
on request.



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

Re: [PHP-DB] Help with JOIN query


Hi!

Graham Cossey wrote:


I was hoping to avoid joining everything as there can be many entries
in TableB for each record in TableA.

Also wouldn't your query only return one record? I need to return all
records from TableA with the latest action from TableB as well.


Yes, sorry - I realised that after I sent away my first reply.. :) I 
have a colleague who always says that "people don't READ their mail - 
the just LOOK at their mail".. It's so true! I missed some of your points.


But maybe you got some ideas from my other mail, which I seem to have 
posted only to you directly.. Oh well, this is a tricky one anyway...


/Krister Karlström

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

Re: [PHP-DB] Help with JOIN query

2008-03-06 Thread Graham Cossey

On Thu, Mar 6, 2008 at 6:54 PM, Krister Karlström
<[EMAIL PROTECTED]> wrote:
> Hi!
>
>
>  Graham Cossey wrote:
>
>  > TableA
>  > record_id
>  > product_ref
>  >
>  > TableB
>  > timestamp
>  > record_id
>  > action
>  >
>  > I want to create a SELECT that joins these 2 tables where the JOIN to
>  > TableB only returns the most recent entry by timestamp.
>
>  For instance, to select all columns:
>
>  select * from TableA
>  join TableB on TableA.record_id = TableB.record_id
>  order by timestamp desc
>  limit 1
>
>  So you just join everything, then order by time in descening order and
>  then just returning the first record = the newest record in the
>  database. If you don't want all columns, then simply replace the star
>  with the names of the columns you want.
>
>  I hope that this is what you wanted the query to do.. :)
>
I was hoping to avoid joining everything as there can be many entries
in TableB for each record in TableA.

Also wouldn't your query only return one record? I need to return all
records from TableA with the latest action from TableB as well.

Graham



-- 
Graham

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

Re: [PHP-DB] Help with JOIN query


Hi!

Graham Cossey wrote:


TableA
record_id
product_ref

TableB
timestamp
record_id
action

I want to create a SELECT that joins these 2 tables where the JOIN to
TableB only returns the most recent entry by timestamp.


For instance, to select all columns:

select * from TableA
join TableB on TableA.record_id = TableB.record_id
order by timestamp desc
limit 1

So you just join everything, then order by time in descening order and 
then just returning the first record = the newest record in the 
database. If you don't want all columns, then simply replace the star 
with the names of the columns you want.


I hope that this is what you wanted the query to do.. :)

Cheers,
Krister Karlström, Helsinki, Finland

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

Re: [PHP-DB] Help with MySql float

2008-02-17 Thread Chris


Daniel Brown wrote:

On Feb 17, 2008 10:59 AM, Velen <[EMAIL PROTECTED]> wrote:

Hi Guys,

When inserting a value like 123567.8956 in my table it is rounding it to 2
decimal place.  The field type is set as float.

Can anyone tell me why it's not taking all the decimals? and How to insert
the number with all the decimals?


That's a MySQL question, not a PHP question, but my guess is that
the column is set as FLOAT(x,2) (where x is any real number).  Try
changing that to FLOAT(10,4).  The first number is everything to the
left of the decimal, while the second is the number of places to count
after the decimal.


Also note that a float type is not guaranteed to come back the same as 
it goes in (and this will also affect maths operations) - so if you need 
4 decimal places, then use decimal or numeric as the data type.


http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

MySQL performs rounding when storing values, so if you insert 999.9 
into a FLOAT(7,4)  column, the approximate result is 999.0001.



The DECIMAL and NUMERIC data types are used to store exact numeric data 
values. . These types are used to store values for which it is 
important to preserve exact precision, for example with monetary data.



This is the same in any db, it's not a mysql specific behaviour.

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

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

Re: [PHP-DB] Help with MySql float

2008-02-17 Thread Daniel Brown

On Feb 17, 2008 10:59 AM, Velen <[EMAIL PROTECTED]> wrote:
> Hi Guys,
>
> When inserting a value like 123567.8956 in my table it is rounding it to 2
> decimal place.  The field type is set as float.
>
> Can anyone tell me why it's not taking all the decimals? and How to insert
> the number with all the decimals?

That's a MySQL question, not a PHP question, but my guess is that
the column is set as FLOAT(x,2) (where x is any real number).  Try
changing that to FLOAT(10,4).  The first number is everything to the
left of the decimal, while the second is the number of places to count
after the decimal.

-- 

Daniel P. Brown
Senior Unix Geek

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

Re: [PHP-DB] Help with MySql float

2008-02-17 Thread Tobias Franzén


Velen wrote:

Hi Guys,

When inserting a value like 123567.8956 in my table it is rounding it to 2
decimal place.  The field type is set as float.

Can anyone tell me why it's not taking all the decimals? and How to insert
the number with all the decimals?

Thanks

Velen

  

Hello Velen,

Your question seem purely related to MySQL. Not very PHP related. Anyway...

It depends on your version of MySQL. Check the documentation for Numeric 
Types. It says you can specify the number of decimals you want to store, 
something like FLOAT(max_digits,precision). If you need more precision, 
there's also the DOUBLE PRECISION.


If your MySQL column is specified for higher precision already, then it 
could be a PHP issue. Is this the case?


For MySQL => 5.0
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

For MySQL < 5.0
http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html


/Tobias

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

Re: [PHP-DB] help

2007-08-20 Thread subramani

On 8/20/07, Asim <[EMAIL PROTECTED]> wrote:
> can anyone provide me code of basic website structure having member
>  area and can use mysql database to show results of search by PAGINATION?

For pagination refer this :- http://www.phpfreaks.com/tutorials/43/5.php
Hope this will help you.

--- R * Subramani
My Log File : http://rsubramani.wordpress.com

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

Re: [PHP-DB] Help creating tables and fields

2007-03-29 Thread bedul

- Original Message -
From: Karl James
To: 'bedul'
Sent: Thursday, March 29, 2007 10:40 AM
Subject: RE: [PHP-DB] Help creating tables and fields

bedul,

wow great information and so quick.
very greatfull for that.

Would you mine baby stepping for me?
[bedul]
i don't understand what u said.. but i read as something nice
my real name was gunawan (pronounce as Gun - A - One )
=
I am using dreamweaver 8 to build my pages.
[bedul]
i'm using not 1!! but 3!! text editor, html editor (dreamweaver) and text
based html editor..
fyi.. i build scratch in excel. that editor was crimson editor (this free) ,
Ace Html (trial).. since u already use DW.. don't try.. you might try to
download it..

plz remember.. u not superman.. we only able to create page using php. in
this era.. u probably able to use html, db, javascript and template
building..
owww man.. fyi.. i'm not that strong created by myself. even i already
create using both of i said earlier.

see attach
u might hard to read the languages i use. but i mean in the attach was..
this is my scratch design
===
But I can do in code as well.
I just created a connection to my database.
So, now I am just trying to create the username and password field
on my dwt template page? Would that be correct or what should be my first
step?
[bedul]
that's better.. but u supposed to create map link for the aplication
there were 3 who use this page.
let view the guest first..
1. guest enter the web (www.fotball.com)
2. in there what he/she able to see (for guest only) => what the scratch of
guest??
3. are there anything else that guest able to see??

then let view the user
1. user enter the web
2. user view opening page
3. login
4. if login failed
5. what he/she view after login
6. other page that he/she able to see

admin
i think.. for this.. self explain.
===
Should we just create the tables first through phpmyadmin page.
That is where it is easier to see everything, the tables that is.
[bedul]
the reason why you must to build the scratch (before goes online and able to
program).. is to figure what table we can use??
after u able to build the scratch page (html).. then u goes to build the
table..
===
Please continue with your thoughts.

Karl James ("TheSaint")
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.theufl.com

[bedul]
last suggestion..
u can improve the skill if you
1. created as many page
2. complain by your user
3. lazy?? i too lazy type textbox and then i create a function that return
textbox
function inpText($name, $dValue, $size, $max){
 $temp ='

resepOnLine.xls
Description: MS-Excel spreadsheet
-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] help in database design

2007-03-28 Thread bedul

NOT QUITE..ups.. my bad
- Original Message - 
From: "Chris" <[EMAIL PROTECTED]>
To: "Suprie" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, March 29, 2007 6:53 AM
Subject: Re: [PHP-DB] help in database design


> Suprie wrote:
> > hi all,
> > 
> > i need help in design a database, our office is hospital equipment 
> > supplier,
> > we had a list of items, each items had it's own configuration, and
> > each item could consist of several item. we also had contract that
> > have a lot of site, sometimes equipmend that have been ordered
> > different from what the site received, in that case we made a change
> > request...
> > 
> > for now we have this
> > 
> > |contract|--|has|-|site|
> >   |
> >   |
> >   |item||material|
> >   |
> >   |
> >  |configuration|
> > 
> > but i really confused about the Change Request thing, we have to keep
> > both what have been ordered and what have been received... where
> > should i put it, sorri for my bad english
> 
> Why does that have to fit into this scheme?
> 
> Why can't it be separate?
well as u mention above.. he already seperated while ago.. 
item and material was 2 different table. and yet it should seperated..

i alreadt received the newest.. i hope he posted but  i posted again
==
site is, the items destination , owh i forgot beside the goods we also
provide services, that could change also just like other items depends
the situation.
=

as i write above.. he mistaken on what the flow goes

> 
> create table change_requests (
>request_id primary key .. blah blah,
>item_ordered text,
>item_order_date timestamp, // so you know when you ordered it
>item_received text,
>item_receive_date timestamp // so you know when you received it.
> );
> 
> problem solved ?


> 
> -- 
> 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 Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] help in database design

2007-03-28 Thread Chris


Suprie wrote:

hi all,

i need help in design a database, our office is hospital equipment 
supplier,

we had a list of items, each items had it's own configuration, and
each item could consist of several item. we also had contract that
have a lot of site, sometimes equipmend that have been ordered
different from what the site received, in that case we made a change
request...

for now we have this

|contract|--|has|-|site|
  |
  |
  |item||material|
  |
  |
 |configuration|

but i really confused about the Change Request thing, we have to keep
both what have been ordered and what have been received... where
should i put it, sorri for my bad english


Why does that have to fit into this scheme?

Why can't it be separate?

create table change_requests (
  request_id primary key .. blah blah,
  item_ordered text,
  item_order_date timestamp, // so you know when you ordered it
  item_received text,
  item_receive_date timestamp // so you know when you received it.
);

problem solved ?

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

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

Re: [PHP-DB] help in database design

2007-03-28 Thread bedul

what is site mean in here??

- Original Message -
From: "Suprie" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, March 28, 2007 5:03 PM
Subject: [PHP-DB] help in database design


> hi all,
>
> i need help in design a database, our office is hospital equipment
supplier,
> we had a list of items, each items had it's own configuration, and
> each item could consist of several item. we also had contract that
> have a lot of site, sometimes equipmend that have been ordered
> different from what the site received, in that case we made a change
> request...
>
> for now we have this
>
> |contract|--|has|-|site|
>|
>|
>|item||material|
>|
>|
>   |configuration|

i rather confused on configuration.. but plz send me a light??


>
> but i really confused about the Change Request thing, we have to keep
> both what have been ordered and what have been received... where
> should i put it, sorri for my bad english
>
> tq

--
-- Table structure for table `rsib_contract`
--

CREATE TABLE `rsib_contract` (
  `conId` int(11) NOT NULL,
  `conItemID` int(11) NOT NULL,
  `conSiteID` int(11) NOT NULL,
  `conDesc` varchar(100) collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `rsib_contract`
--


-- 

--
-- Table structure for table `rsib_item`
--

CREATE TABLE `rsib_item` (
  `itemId` int(11) NOT NULL,
  `itemName` varchar(45) collate latin1_general_ci NOT NULL,
  `itemStat` varchar(4) collate latin1_general_ci NOT NULL,
  `itemDesc` text collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `rsib_item`
--


-- 

--
-- Table structure for table `rsib_material`
--

CREATE TABLE `rsib_material` (
  `matId` int(11) NOT NULL,
  `matName` varchar(34) collate latin1_general_ci NOT NULL,
  `matDesc` text collate latin1_general_ci NOT NULL,
  `matItemId` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `rsib_material`
--


-- 

--
-- Table structure for table `rsib_site`
--

CREATE TABLE `rsib_site` (
  `siteId` int(11) NOT NULL,
  `siteName` varchar(60) collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;



>
> --
> Jangan tanyakan apa yang Indonesia telah berikan pada mu
> tapi bertanyalah apa yang telah engkau berikan kepada Indonesia
>
> -BEGIN GEEK CODE BLOCK-
> Version: 3.1
> GU/IT  d- s: a-- C++ UL P L++ E W++ N* o-- K-
> w PS  Y-- PGP- t++ 5 X R++ tv  b+ DI D+ G e+ h* r- z?
>  --END GEEK CODE BLOCK--
>
> --
> 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] help with mysql connect error

2007-02-11 Thread Chris


Tim McGeary wrote:



Chris wrote:

Tim McGeary wrote:

Stut wrote:

Ok, so I did the recommended process of:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

This allows the CLI script to run successfully, but the web php file 
still gives me the original error.  Any ideas?  The error again is:


Warning: mysql_connect(): Can't connect to local MySQL server through 
socket '/var/lib/mysql/mysql.sock' (13) in 
/var/www/html/software/index.php on line 18
Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (13)


'13' means permission denied.

$ perror 13
System error:  13 = Permission denied

What are the permissions on the /var/lib/mysql/ directory? Maybe php 
can't get into that, thus it can't get to the socket file.


Try

chmod 755 /var/lib/mysql

to allow any user to be able to read files in that folder (but not 
change anything)...


You are correct, it was the directory permissions!  Thank you so much. 
That is also great information about what the error numbers mean.  Where 
or how can I find out what those numbers mean for future reference?


With a *nix / *bsd shell, pass it to 'perror':

perror XXX

and if perror knows what it is, it will let you know... mysql (or other 
apps) might use numbers that perror doesn't know, then it becomes a bit 
tougher but a search usually helps.


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

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

Re: [PHP-DB] help with mysql connect error

2007-02-09 Thread Tim McGeary




Chris wrote:

Tim McGeary wrote:

Stut wrote:

Ok, so I did the recommended process of:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

This allows the CLI script to run successfully, but the web php file 
still gives me the original error.  Any ideas?  The error again is:


Warning: mysql_connect(): Can't connect to local MySQL server through 
socket '/var/lib/mysql/mysql.sock' (13) in 
/var/www/html/software/index.php on line 18
Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (13)


'13' means permission denied.

$ perror 13
System error:  13 = Permission denied

What are the permissions on the /var/lib/mysql/ directory? Maybe php 
can't get into that, thus it can't get to the socket file.


Try

chmod 755 /var/lib/mysql

to allow any user to be able to read files in that folder (but not 
change anything)...


You are correct, it was the directory permissions!  Thank you so much. 
That is also great information about what the error numbers mean.  Where 
or how can I find out what those numbers mean for future reference?


Thanks,
Tim


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

Re: [PHP-DB] help with mysql connect error

2007-02-08 Thread Chris


Tim McGeary wrote:

Stut wrote:

Tim McGeary wrote:

But I do have a MySQL 5 client:

[EMAIL PROTECTED] html]# rpm -qa MySQL*
MySQL-shared-compat-5.0.27-0.rhel3
MySQL-client-standard-5.0.27-0.rhel3
MySQL-python-0.9.1-6
MySQL-server-standard-5.0.27-0.rhel3
MySQL-devel-standard-5.0.27-0.rhel3

or are you saying that the PHP libs have a MySQL 4 client in there?  
If that is the case, how should I rebuild PHP?


Yes, and it depends on what OS, where it originally came from, etc. 
The alternative is to change the password of the MySQL user you're 
using - the link in my last post explains how to do this.


Ok, so I did the recommended process of:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

This allows the CLI script to run successfully, but the web php file 
still gives me the original error.  Any ideas?  The error again is:


Warning: mysql_connect(): Can't connect to local MySQL server through 
socket '/var/lib/mysql/mysql.sock' (13) in 
/var/www/html/software/index.php on line 18
Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (13)


'13' means permission denied.

$ perror 13
System error:  13 = Permission denied

What are the permissions on the /var/lib/mysql/ directory? Maybe php 
can't get into that, thus it can't get to the socket file.


Try

chmod 755 /var/lib/mysql

to allow any user to be able to read files in that folder (but not 
change anything)...


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

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

Re: [PHP-DB] help with mysql connect error


Stut wrote:

Tim McGeary wrote:

But I do have a MySQL 5 client:

[EMAIL PROTECTED] html]# rpm -qa MySQL*
MySQL-shared-compat-5.0.27-0.rhel3
MySQL-client-standard-5.0.27-0.rhel3
MySQL-python-0.9.1-6
MySQL-server-standard-5.0.27-0.rhel3
MySQL-devel-standard-5.0.27-0.rhel3

or are you saying that the PHP libs have a MySQL 4 client in there?  
If that is the case, how should I rebuild PHP?


Yes, and it depends on what OS, where it originally came from, etc. The 
alternative is to change the password of the MySQL user you're using - 
the link in my last post explains how to do this.


Ok, so I did the recommended process of:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

This allows the CLI script to run successfully, but the web php file 
still gives me the original error.  Any ideas?  The error again is:


Warning: mysql_connect(): Can't connect to local MySQL server through 
socket '/var/lib/mysql/mysql.sock' (13) in 
/var/www/html/software/index.php on line 18
Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (13)


It definitely looks like a connection problem, as it isn't getting a 
chance to login.


Tim

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

Re: [PHP-DB] help with mysql connect error


Tim McGeary wrote:
Thank you for the link.  I think that's probably the best way to go in 
this case, since it's only on a dev/test server.  When I build the 
production server, I should probably upgrade to PHP5 anyhow.  I assume 
that should not have this problem, right?


It might. PHP5 can be built against libs for MySQL 4 or 5, so just make 
sure that you match the version used for PHP to the server version and 
you'll be fine.



Thanks for your help, Stut!!!


No probs.

-Stut

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

Re: [PHP-DB] help with mysql connect error


Stut wrote:

Tim McGeary wrote:

But I do have a MySQL 5 client:

[EMAIL PROTECTED] html]# rpm -qa MySQL*
MySQL-shared-compat-5.0.27-0.rhel3
MySQL-client-standard-5.0.27-0.rhel3
MySQL-python-0.9.1-6
MySQL-server-standard-5.0.27-0.rhel3
MySQL-devel-standard-5.0.27-0.rhel3

or are you saying that the PHP libs have a MySQL 4 client in there?  
If that is the case, how should I rebuild PHP?


Yes, and it depends on what OS, where it originally came from, etc. The 
alternative is to change the password of the MySQL user you're using - 
the link in my last post explains how to do this.


Thank you for the link.  I think that's probably the best way to go in 
this case, since it's only on a dev/test server.  When I build the 
production server, I should probably upgrade to PHP5 anyhow.  I assume 
that should not have this problem, right?


Thanks for your help, Stut!!!

Tim

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

Re: [PHP-DB] help with mysql connect error


Tim McGeary wrote:

But I do have a MySQL 5 client:

[EMAIL PROTECTED] html]# rpm -qa MySQL*
MySQL-shared-compat-5.0.27-0.rhel3
MySQL-client-standard-5.0.27-0.rhel3
MySQL-python-0.9.1-6
MySQL-server-standard-5.0.27-0.rhel3
MySQL-devel-standard-5.0.27-0.rhel3

or are you saying that the PHP libs have a MySQL 4 client in there?  If 
that is the case, how should I rebuild PHP?


Yes, and it depends on what OS, where it originally came from, etc. The 
alternative is to change the password of the MySQL user you're using - 
the link in my last post explains how to do this.


-Stut

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

Re: [PHP-DB] help with mysql connect error




Stut wrote:

Tim McGeary wrote:

Oh, duh!  Ok.  I wrote this:


mysql_connect($hostname, $username, $password) or 
die(mysql_error());

mysql_select_db($dbname) or die(mysql_error());

$result = mysql_query("SELECT * FROM Requestor_type") or 
die(mysql_error());

while($row = mysql_fetch_array( $result )) {
echo 'value="'.$row['description'].'">'.$row['description']." ";

}
?>


And got this as output:



Warning:  mysql_connect(): Client does not support 
authentication protocol requested by server; consider upgrading MySQL 
client in /var/www/html/mysql_test.php on line 7
Client does not support authentication protocol requested by server; 
consider upgrading MySQL client


I'm not sure I understand it.  Maybe my CLI PHP script is not correct? 
I'm returning to PHP after 5 years of not writing in it.


This basically means that you're trying to access a MySQL 5 server with 
MySQL 4 client. You have two choices...


1) Rebuild PHP with the MySQL 5 client libs
2) Use the old password mechanism in MySQL

See here for more info...

http://dev.mysql.com/doc/refman/5.0/en/old-client.html


But I do have a MySQL 5 client:

[EMAIL PROTECTED] html]# rpm -qa MySQL*
MySQL-shared-compat-5.0.27-0.rhel3
MySQL-client-standard-5.0.27-0.rhel3
MySQL-python-0.9.1-6
MySQL-server-standard-5.0.27-0.rhel3
MySQL-devel-standard-5.0.27-0.rhel3

or are you saying that the PHP libs have a MySQL 4 client in there?  If 
that is the case, how should I rebuild PHP?


Tim

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

Re: [PHP-DB] help with mysql connect error


Tim McGeary wrote:

Oh, duh!  Ok.  I wrote this:


mysql_connect($hostname, $username, $password) or 
die(mysql_error());

mysql_select_db($dbname) or die(mysql_error());

$result = mysql_query("SELECT * FROM Requestor_type") or 
die(mysql_error());

while($row = mysql_fetch_array( $result )) {
echo 'value="'.$row['description'].'">'.$row['description']." ";

}
?>


And got this as output:



Warning:  mysql_connect(): Client does not support 
authentication protocol requested by server; consider upgrading MySQL 
client in /var/www/html/mysql_test.php on line 7
Client does not support authentication protocol requested by server; 
consider upgrading MySQL client


I'm not sure I understand it.  Maybe my CLI PHP script is not correct? 
I'm returning to PHP after 5 years of not writing in it.


This basically means that you're trying to access a MySQL 5 server with 
MySQL 4 client. You have two choices...


1) Rebuild PHP with the MySQL 5 client libs
2) Use the old password mechanism in MySQL

See here for more info...

http://dev.mysql.com/doc/refman/5.0/en/old-client.html

-Stut

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

Re: [PHP-DB] help with mysql connect error

Stut wrote:

Tim McGeary wrote:

Stut wrote:

Tim McGeary wrote:
I am new to this list today, so if I should be sending this to
another

specific PHP list, please let me know.

I am getting the following error via the PHP web page I am building:

Warning: mysql_connect(): Can't connect to local MySQL server
through

socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/software/index.php on line 18 Can't connect to local
MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

Given that you can connect through the socket on the CLI, I'm gonna
guess that it's a permissions issue. Does the user that Apache (or
whatever web server you're using) runs as have access to mysql.sock?

Currently mysql.sock is owned by mysql.mysql with S777 permissions.
Should the ownership be different? Despite the ownership, wouldn't
S777 allow any user to access it?

Indeed it should. Have you tried writing a CLI script and seeing if
that works?

I confess I don't know what at CLI script is. What would I write?

To be run on the command line. Same as a web-based PHP script, but no
need to output any HTML. Just write a script that tries to connect to
the database and spits out success or failure. Then run php script.php
on the command line (CLI === Command Line Interface) and see what happens.

-Stut

Oh, duh! Ok. I wrote this:

'.$row['description']." ";
}
?>

And got this as output:

Warning: mysql_connect(): Client does not support authentication protocol requested by server;
consider upgrading MySQL client in /var/www/html/mysql_test.php on line 7
Client does not support authentication protocol requested by server; consider
upgrading MySQL client

I'm not sure I understand it. Maybe my CLI PHP script is not correct?
I'm returning to PHP after 5 years of not writing in it.

Tim

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

Re: [PHP-DB] help with mysql connect error


Tim McGeary wrote:

Please include the list in replies.


Sorry, I meant to, but hit the wrong button.


Tim McGeary wrote:

Stut wrote:

Tim McGeary wrote:

I am new to this list today, so if I should be sending this to another
specific PHP list, please let me know.

I am getting the following error via the PHP web page I am building:


Warning: mysql_connect(): Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/software/index.php on line 18 Can't connect to local
MySQL server through socket '/var/lib/mysql/mysql.sock' (13)


Given that you can connect through the socket on the CLI, I'm gonna 
guess that it's a permissions issue. Does the user that Apache (or 
whatever web server you're using) runs as have access to mysql.sock?


Currently mysql.sock is owned by mysql.mysql with S777 permissions. 
Should the ownership be different?  Despite the ownership, wouldn't 
S777 allow any user to access it?


Indeed it should. Have you tried writing a CLI script and seeing if 
that works?


I confess I don't know what at CLI script is.  What would I write?


To be run on the command line. Same as a web-based PHP script, but no 
need to output any HTML. Just write a script that tries to connect to 
the database and spits out success or failure. Then run php script.php 
on the command line (CLI === Command Line Interface) and see what happens.


-Stut

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

Re: [PHP-DB] help with mysql connect error


Please include the list in replies.


Sorry, I meant to, but hit the wrong button.


Tim McGeary wrote:

Stut wrote:

Tim McGeary wrote:

I am new to this list today, so if I should be sending this to another
specific PHP list, please let me know.

I am getting the following error via the PHP web page I am building:


Warning: mysql_connect(): Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/software/index.php on line 18 Can't connect to local
MySQL server through socket '/var/lib/mysql/mysql.sock' (13)


Given that you can connect through the socket on the CLI, I'm gonna 
guess that it's a permissions issue. Does the user that Apache (or 
whatever web server you're using) runs as have access to mysql.sock?


Currently mysql.sock is owned by mysql.mysql with S777 permissions. 
Should the ownership be different?  Despite the ownership, wouldn't 
S777 allow any user to access it?


Indeed it should. Have you tried writing a CLI script and seeing if that 
works?


I confess I don't know what at CLI script is.  What would I write?

Tim


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

Re: [PHP-DB] help with mysql connect error


Stut wrote:

Tim McGeary wrote:

I am new to this list today, so if I should be sending this to another
specific PHP list, please let me know.

I am getting the following error via the PHP web page I am building:


Warning: mysql_connect(): Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/software/index.php on line 18 Can't connect to local
MySQL server through socket '/var/lib/mysql/mysql.sock' (13)


Given that you can connect through the socket on the CLI, I'm gonna 
guess that it's a permissions issue. Does the user that Apache (or 
whatever web server you're using) runs as have access to mysql.sock?




Currently mysql.sock is owned by mysql.mysql with S777 permissions.
Should the ownership be different?  Despite the ownership, wouldn't S777
allow any user to access it?

Tim



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

Re: [PHP-DB] help with mysql connect error