Re: [PHP-DB] newbie help

[Bob Chatman]

This is definitely not the best advice in the world. The truth is you could
staple your eye lids open and never blink, but it wouldn't be very
beneficial. I highly suggest you invest some of your time in reading about
databases and how relational databases work, and more than that how they
work well, instead of trying to create some monster that will end up causing
you more stress than income.

I would also suggest you sit down and try to plan out whatever the hell you
are trying to do before you get too crazy. If you code like you describe
things here you are bound to confuse variable names and thats just crazy.
Try to clean up your idea and ill gladly direct you to some of your options.


and most of all, dont try to do this as TG has described here.

On 9/28/07, TG <[EMAIL PROTECTED]> wrote:
>
>
> How resource intensive it is depends a lot on how much data you have and
> what
> you're doing with it.   You want to reduce the number of connections and
> disconnects, since those are really intensive, but if everything is on the
> same server, you can do cross database queries.
>
> As for the table size exceeding the OS limit, that'd take quite a bit of
> data
> to do.   With proper database design, that shouldn't be too much of an
> issue.   If you have 2+gb of data in a single table, that's one monster
> table.
>
> Some info here about table size restrictions for MySQL:
> http://dev.mysql.com/doc/refman/4.1/en/full-table.html
>
> You could create a table and stuff it full of thousands of records and try
> to
> check the physical file size of that table's file and see how tricky it
> would be to actually 'fill' a table.
>
>
> Ideally, for your 'customer' table would contain just the data that was
> unique to that customer.  Typically data that all your customers are going
> to have a single entry for so you don't have a lot of empty spots.
> Anything that you have multiple entries for or are unique to a single
> customer, you could put in another table as meta data (additional
> information that didn't fit in the main table).
>
> You probably want to ignore my original parent/child threading idea.  I
> was
> just illustrating a way that you COULD do it.
>
> What you may want to do is have a 'customers' table, which contain your
> customers, then another table for the base information for your customer's
> customers with an ID field that matches the entry in your 'customers'
> table.
>
> For additional tables in the database, you could do something like you
> meantioned... have "custname1_tablename1" and use a code in your
> "customers" table to indicate the table prefix.
>
> This kind of thing is used a lot in systems like phpBB, Gallery, etc so
> you
> can have multiple copies installed on the same server.  It'd be the same
> idea when dealing with multiple customers.
>
> Just make sure that the prefix you use for a customer doesn't use
> characters
> that are bad for table names.
>
> What kind of data are you thinking about storing for your customers and
> your
> customer's customers?  And what other data do you need to store for
> whatever your webapp does?
>
> -TG
>
>
> - Original Message -
> From: nhadie <[EMAIL PROTECTED]>
> To: php-db@lists.php.net
> Date: Fri, 28 Sep 2007 23:49:23 +0800
> Subject: Re: [PHP-DB] newbie help
>
> > Thanks for your reply, which solution would be faster and not put too
> > much load on the resources of the server? single or multiple database?
> > If i use a single database, would i be having same tables for each
> > customer, e.g. if i have customerinfo table for customers of A,
> > would i also have customerinfo table on customer 1 and 2 and so on, but
> > changing the name by prefixing something to the table name e.g
> > cust1_customerinfo.
> > i'm thinking if i use only a single table for, then i might have
> > problems on the file size limit of the OS, would i have that problem?
> >
> > thanks again for your help, i really,really appreciate it.
> >
> > regards,
> > nhadie
> >
> >
> >
> >
> > TG wrote:
> > > You could do separate databases if you want.   It all depends on how
> you
> > want
> > > to organize your data (keeping in mind data backup strategies and
> stuff).
> > >
> > > Using separate databases on the same server, you might have another
> > database
> > > for the 'common' data.  Any variables you store in the database that
> > don't
> > > really change and are common to all databases.  Or if you log anything
> > and
> > > want to keep the log data in one central location, you could put it in
> > the
> > > 'common' database.
> > >
> > >
> > > If you wanted to store all the data in one database, you could use
> > something
> > > like forum threading techniques to show a parent/child relationship
> for
> > the
> > > customers.
> > >
> > >
> > > id  nameparent
> > > 1   customerANULL
> > > 2   cust11
> > > 3   cust21
> > > 4   custB   2
> > > 5 

Re: [PHP-DB] A good PHP Ajax tutorial

[bob plano]

well i found a site that has a lot of web building tutorials on it. i
havent looked much at them though. the site is
http://www.w3schools.com/default.asp

On 2/24/07, Denis L. Menezes <[EMAIL PROTECTED]> wrote:

Dear friends.

Can someone please suggest a good pHP/Ajax tutorial?

Thanks
Denis

--
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] Session Problem

[bob plano]

what version of php are you using? i ran across a problem with
sessions not working for windows in php 5.2 because the directories
for the sessions were not created.

On 1/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I don't think that the problem is on the browser setting...

Cause I've tried to browse the other web application (that using session
too) on other server, and its running well...



Forget to tell you, that if I used http://localhost 
, the session running well...

But if I change with http://hostname   , the session
wasn't running...

I thought the problem was on privileges...

But I have setting the grant privilege to user root & the hostname...

Still the session not running..



Regards,



Anita



-Original Message-
From: Bastien Koert [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 13, 2007 12:21 AM
To: Adman, Nur anita; [EMAIL PROTECTED]
Cc: php-db@lists.php.net
Subject: RE: [PHP-DB] Session Problem



are you sure that the problem is not in the browser settings (not to
accept

session cookies)?



Bastien





>From: <[EMAIL PROTECTED]>

>To: <[EMAIL PROTECTED]>

>CC: 

>Subject: RE: [PHP-DB] Session Problem

>Date: Fri, 12 Jan 2007 15:34:06 +0900

>

>

>

>1.  Did you mean that on phpinfo(), the Session Support is enabled?

>Yes, it wrote so.

>

>2.  I have check the session.save_files, and its writable. I found

>some files session in there.

>

>

>

>Please advice...

>

>

>

>Thanks & Regards,

>

>

>

>Anita

>

>

>

>

>

>-Original Message-

>From: Peter Beckman [mailto:[EMAIL PROTECTED]

>Sent: Friday, January 12, 2007 2:27 PM

>To: Adman, Nur anita

>Cc: php-db@lists.php.net

>Subject: Re: [PHP-DB] Session Problem

>

>

>

>1. Is session support compiled into PHP, or if not, is the module there

>and

>

>being loaded?  phpinfo() will tell you.

>

>

>

>   If not, recompile PHP or build the dynamic module to support
sessions.

>

>

>

>2. If so, check the location of session.save_files and make sure it is

>

>writable by the web server user/group.  If not, you must change this.

>

>

>

>Beckman

>

>

>

>On Fri, 12 Jan 2007, [EMAIL PROTECTED] wrote:

>

>

>

> > Dear All,

>

> >

>

> >

>

> >

>

> > I need some help please...

>

> >

>

> >

>

> >

>

> > I'm developing an web application using PHP/MySQL, and its has been

>

> > running on my company.

>

> >

>

> >

>

> >

>

> > I'm planning to migrate the system on a new server.

>

> >

>

> > I have installed everything that are needed on the new server to

>running

>

> > the web application.

>

> >

>

> > But I'm confusing that the session isn't running.

>

> >

>

> >

>

> >

>

> > For your information, I've setting all configurations (MySQL & PHP),

>

> > included register globals to "Off".

>

> >

>

> > I have trying to re-install the web server & MySQL, but it still not

>

> > running...

>

> >

>

> >

>

> >

>

> > Please advice...

>

> >

>

> >

>

> >

>

> > Thanks & Regards,

>

> >

>

> >

>

> >

>

> > Anita

>

> >

>

> >

>

>

>

>---
-

>---

>

>Peter Beckman  Internet

>Guy

>

>[EMAIL PROTECTED]

>http://www.purplecow.com/

>

>---
-

>---

>



_

Your opinion matters. Please tell us what you think and be entered into
a

draw for a grand prize of $500 or one of 20 $50 cash prizes.

http://www.youthographyinsiders.com/R.aspx?a=116







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

Re: [PHP-DB] mysql databases

[bob plano]

sorry, i meant that i wanted to remove the oldest entry and put in a
new entry with UPDATE or INSERT.

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

[PHP-DB] mysql databases

[bob plano]

how would you add something new into a table and take out something
old? example:

(1) 1st something
(2) 2nd something
(3) 3rd something
(4) 4th something


and then you add in something new so then the table would look like

(1) new something
(2) 1st something
(3) 2nd something
(4) 3rd something


thanks in advance

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

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

[Blanton, Bob]

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


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

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

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

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

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

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

[Blanton, Bob]

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

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

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




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

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

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

And you want to output something like:

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

But what you're getting is:

Joe 123
Joe 124
..etc

You have two ways you can solve this:

1. Do two SQL queries:

SELECT DISTINCT cus_name FROM customers

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

Or..

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

SELECT cus_name, cus_id FROM customers

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

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

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

Good luck!

-TG

= = = Original message = = =

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

here is algorithm;

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



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

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

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

[PHP-DB] SSL implementation

[Blanton, Bob]

Hi,
  I'm new to web programming and Apache & PHP so this may seem like a
dumb question.  Is there a preferred way to implement SSL in a website
or web application?  Is it handled using PHP or Apache?  Or both?  I see
there is the Apache Open SSL server and the mod_ssl.  Seems there are
many options to choose from.  

  Does anyone have any Pros/Cons on the subject of which implementation
is best?

  Thanks for your comments,
Bob
  

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

RE: [PHP-DB] PHP5-MySQL Not Connecting Through Socket

[Bob Allen]

Comment out the bind-address this allows mysqld to bind to all IPs;
accordingly determine if you only want it local or externally available.
Finally attempt to connect to the mysql server using the 
mysql -h127.0.0.1 -u -p 
mysql -h -u -p 
mysql -hlocalhost -u -p

you may find the user permissions are the problem. Even when you connect
locally mysql sees these as connections the user is not allowed to
connect from.

Robert Allen, zce
Operations, FIO Labs, LLC

-Original Message-
From: Evan J [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 11:41 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] RE: PHP5-MySQL Not Connecting Through Socket

Thank you Robert for the prompt response. 

1) Yes, I do have /etc/my.cnf (644 permission)

2) I do have a bind-address directive but it was set to "127.0.0.0" by
default. I even changed it to "127.0.0.1" and restarted MySQL; no
luck.

3) my.cnf's socket file path and name in both [client] and [mysqld]
are the same and have been set accordingly.

I am still stumped as of why PHP cannot connect through Unix socket to
MySQL!

On 8/30/05, Bob Allen <[EMAIL PROTECTED]> wrote:
> 1) Do you have a /etc/my.cnf ?
> 2) In this do you have the bind-address directive set?
>  i.e. [ bind-address = 127.0.0.1] this is the default for
debian
> packages versions.
> 3) Also confirm the my.cnf reflects the mysql.sock path properly as
> well.
> 
> Robert Allen, zce
> Operations, FIO Labs LLC
> 
> 
> -Original Message-
> From: Evan J [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 30, 2005 7:57 AM
> To: php-db@lists.php.net
> Subject: PHP5-MySQL Not Connecting Through Socket
> 
> Hi,
> 
> I am running PHP 5.0.4 and MySQL 4.1.3a. (+Apache 2.0.54) When I run a
> PHP script that attempts to connect to MySQL I get the following
> error:
> 
> Database ERROR:Can't connect to local MySQL server through socket
> '/usr/local/mysql/tmp/mysql.sock' (13)
> 
> I have placed my socket file on such location when I was configuring
> MySQL and PHP was compiled with
> --with-mysql-sock=/usr/local/mysql/tmp/mysql.sock (and even with
> /usr/local/mysql/tmp). I even edited my php.ini
> (/usr/local/php/lib/php.ini, I have installed PHP with
> --prefix=/usr/local/php) and modified variable:
> 
> mysql.default_socket = "/usr/local/mysql/tmp/mysql.sock"
> 
> It still refuses to connect through the socket! I have also run a
> simple mysql_connect() script to see if the intended script was the
> culprit but again, it could not connect to MySQL.
> 
> phpinfo() indicates the following:
> 
> MYSQL_SOCKET/usr/local/mysql/tmp/mysql.sock
> MYSQL_INCLUDE   -I/usr/local/mysql/include/mysql
> MYSQL_LIBS  -L/usr/local/mysql/lib/mysql -lmysqlclient
> 
> And for directives:
> mysql.default_port  no valueno value
> mysql.default_socket/usr/local/mysql/tmp/mysql.sock
> /usr/local/mysql/tmp/mysql.sock
> 
> That being said, I "CAN" connect to the database via "127.0.0.1"
> through TCP/IP but not through Unix Socket file. The permissions on
> mysql.sock is:
> 
> ls -la /usr/local/mysql/tmp/
> total 0
> drwxrwx---   2 mysql mysql  80 2005-08-24 22:09 ./
> drwxr-xr-x  13 root  mysql 328 2005-08-22 20:03 ../
> srwxrwxrwx   1 mysql mysql   0 2005-08-24 22:09 mysql.sock=
> 
> Again, I can connect to DB through other programs (non-php) or via
> commandline and even TCP but not through Unix socket file!!
> 
> Any ideas?
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
>

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

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

[PHP-DB] RE: PHP5-MySQL Not Connecting Through Socket

[Bob Allen]

1) Do you have a /etc/my.cnf ?
2) In this do you have the bind-address directive set?  
 i.e. [ bind-address = 127.0.0.1] this is the default for debian
packages versions. 
3) Also confirm the my.cnf reflects the mysql.sock path properly as
well.

Robert Allen, zce
Operations, FIO Labs LLC


-Original Message-
From: Evan J [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 7:57 AM
To: php-db@lists.php.net
Subject: PHP5-MySQL Not Connecting Through Socket

Hi,

I am running PHP 5.0.4 and MySQL 4.1.3a. (+Apache 2.0.54) When I run a
PHP script that attempts to connect to MySQL I get the following
error:

Database ERROR:Can't connect to local MySQL server through socket
'/usr/local/mysql/tmp/mysql.sock' (13)

I have placed my socket file on such location when I was configuring
MySQL and PHP was compiled with
--with-mysql-sock=/usr/local/mysql/tmp/mysql.sock (and even with
/usr/local/mysql/tmp). I even edited my php.ini
(/usr/local/php/lib/php.ini, I have installed PHP with
--prefix=/usr/local/php) and modified variable:

mysql.default_socket = "/usr/local/mysql/tmp/mysql.sock"

It still refuses to connect through the socket! I have also run a
simple mysql_connect() script to see if the intended script was the
culprit but again, it could not connect to MySQL.

phpinfo() indicates the following:

MYSQL_SOCKET/usr/local/mysql/tmp/mysql.sock
MYSQL_INCLUDE   -I/usr/local/mysql/include/mysql
MYSQL_LIBS  -L/usr/local/mysql/lib/mysql -lmysqlclient

And for directives:
mysql.default_port  no valueno value
mysql.default_socket/usr/local/mysql/tmp/mysql.sock
/usr/local/mysql/tmp/mysql.sock

That being said, I "CAN" connect to the database via "127.0.0.1"
through TCP/IP but not through Unix Socket file. The permissions on
mysql.sock is:

ls -la /usr/local/mysql/tmp/
total 0
drwxrwx---   2 mysql mysql  80 2005-08-24 22:09 ./
drwxr-xr-x  13 root  mysql 328 2005-08-22 20:03 ../
srwxrwxrwx   1 mysql mysql   0 2005-08-24 22:09 mysql.sock=

Again, I can connect to DB through other programs (non-php) or via
commandline and even TCP but not through Unix socket file!!

Any ideas?

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

RE: [PHP-DB] password in md5 to connect to mysql instead of clear password

[Bob Sherer]

You could programmatically build the connection string in the php connection 
file, couldn't you?  Have a line that sets a variable equal to the MD5 hashed 
value.  Then, build the connection string, applying a call to a function that 
unhashes the password.  That way, the password itself never appears in code.

I don't know a thing about unhashing MD5 encrypted strings.  Sorry I can't help 
you there.  But, it sounds like you've already found that info.

Good luck,

Bob Sherer

-Original Message-
From: Jon-Eirik Pettersen [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 24, 2005 10:54 AM
To: Gael Lams
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] password in md5 to connect to mysql instead of
clear password


On Thu, 24 Feb 2005 02:37:01 -0800 (PST), Gael Lams <[EMAIL PROTECTED]> wrote:
> Hi all
> 
> I use the classic following rows to connect to a mysql
> database. I always put $passsword in clear in the php
> connection file and I wonder whether there is a way to
> have it in md5 so that someone reading the file could
> not use it to connect to the db. I googled a bit but
> find only threads explaining how to have password
> saved in md5 inside a mysql table which is not I would
> like to do

Because MySQL is using another password-hashing-algoritm other than
MD5, as far as I know, it is not possible.

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

[PHP-DB] Pear DB and DB_DataObject : recommended?

[Bob Sherer]

Howdy.
 
The question: How commonly accepted and recommended are the PEAR packages DB and 
DB_DataObject?  Anybody have some warnings or encouraging stories from projects done 
with these php enhancement packs?
 
I'm about to get started on a php and postgreSQL project that is starting from 
scratch.  I'm relatively new to php, having only done code maintenance on other 
projects while working in a variety of other development environments (mainly .asp.)  
So, I'd rather not waste time learning to use these tools if they aren't something 
that other developers will be happy using as others are brought onto this project.
 
Thanks for your thoughts and opinions,
 
Bob S.

[PHP-DB] RE: Session Values Change

[Bob Sherer]

Jacob,

Here is my solution.
At the top, it checks to see if you are passing a new name via a querystring.  
If not, it checks to see if a name is present in the session yet.  If not, it 
initializes the session to "Jacob".  It puts the session's value into the variable 
$view.
If you passed a name, it stores the name in the session and has it in the variable 
$view.

Now, $view will have a name that the SQL statement can use.

At the bottom, where you choose a name, it simply passes the name selection back to 
the page via a querystring field.

Try the following (notice the changes at the top and the links around the names at the 
bottom):

 


 #FORMATTING CODE HERE
 $dbuser='php' ;
 $dbpass='';
 $dbhost='localhost';
   
 $conn = mysql_connect ( $dbhost , $dbuser , $dbpass );
 //mysql_select_db ( "web" );
 $sql = 'Select UNIX_TIMESTAMP(date) AS
date,title,article from web.blog where user="'.$view.'" order by date desc
LIMIT 10';
 $result = mysql_query($sql);
//Execute For Future Use: SELECT
DATE_FORMAT(date,'%W, %D %M, %Y %l:%i%p') content FROM blog
   
  while ( $dataRow = mysql_fetch_row ( $result ) ) {
   
 $date=date("F dS Y h:i:s A", $dataRow[0]);
   
  echo '';
  echo '  '.$date. '';
  echo '  '.$dataRow[1].'';
  echo '  '.$dataRow[2].'';
  #echo $sql ;
  }
  mysql_close ( $conn );

#FORMATTING CODE HERE

   

Other Users:

Jacob Hackamack | Julie Hackamack | David Hackamack


-Original Message-
From: Jacob Hackamack [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 10:21 PM
To: [EMAIL PROTECTED]
Subject: Session Values Change


Hello,  

I am trying to start a blog page where the person selects their person they
want to view (bottom of the page) and then it somehow changes the session
variable (top of the code) and then accesses the database.  I was wondering,
what is the correct way to do something like this.  I have tried a couple of
options (a hrefs) but they didn¹t seem to work and was wondering if anybody
had any suggestions.

Thank You In Advance

Jacob





 #FORMATTING CODE HERE
 $dbuser='php' ;
 $dbpass='';
 $dbhost='localhost';
   
 $conn = mysql_connect ( $dbhost , $dbuser , $dbpass );
 //mysql_select_db ( "web" );
 $sql = 'Select UNIX_TIMESTAMP(date) AS
date,title,article from web.blog where user="'.$view.'" order by date desc
LIMIT 10';
 $result = mysql_query($sql);
//Execute For Future Use: SELECT
DATE_FORMAT(date,'%W, %D %M, %Y %l:%i%p') content FROM blog
   
  while ( $dataRow = mysql_fetch_row ( $result ) ) {
   
 $date=date("F dS Y h:i:s A", $dataRow[0]);
   
  echo '';
  echo '  '.$date. '';
  echo '  '.$dataRow[1].'';
  echo '  '.$dataRow[2].'';
  #echo $sql ;
  }
  mysql_close ( $conn );

#FORMATTING CODE HERE

   

Other Users:

Jacob Hackamack | Julie Hackamack | David Hackamack


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

[PHP-DB] RE: What's wrong with this QUERY?? - Thanks all.

[Bob Sherer]

Harmeet,

The field email is obviously a field that can accept strings.  In SQL, you must wrap 
strings in single-quotes.  So, rewrite your query as:

$query = "SELECT id, email, familyname FROM members WHERE email='$thing'";

The reason it worked for id=$thing is that the id field is probably an integer field 
and integers do not require quotes.

Remember, wrap your strings in quotes and don't wrap your numbers (unless the number 
is really a string in disquise.)

Good luck,
Bob

-Original Message-
From: Harry G [mailto:harry (removethis)@gabha.net]
Sent: Thursday, July 22, 2004 10:59 PM
To: [EMAIL PROTECTED]
Subject: Re: What's wrong with this QUERY?? - Thanks all.


Thank you everybody.

"Harry G" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I have a database with members details and PK is id.
>
> $thing = "[EMAIL PROTECTED]";
>
> $query = "SELECT id, email, familyname FROM members WHERE email=$thing";
> $result = mysql_query($query);
>
> If i do a query where id=$thing.
> and $thing="20";
> this works fine and I get the desired result. But what is wrong with the
> other one, when I do search for the email address??
>
> The email address does exist exactly as quoted above in the "email" field
in
> my "members" table but still doesn't produce any results.
>
> Any help is much appreciated.
>
> Harmeet

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

[PHP-DB] new row object

[Bob Lockie]

I use $row = fetch_row_object, array_push($row) and I want to manually 
insert a row at the end of my array to indicate if there are more rows.

Is there a way to create a row object?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] mysql limit

[Bob Lockie]

If I select rows with a limit clause I need to know if there are more 
rows than the limit.

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

Re: [PHP-DB] mysql_fetch_object

[Bob Lockie]

How does it handle fully qualified conflicting columns, like 'select 
a.name, b.name from a, b'?

It won't handle that properly; in your example $row->name would be 
b.name (it'd overwrite a.name).  You have to alias same-named columns.

Hans
Ok, I don't have that case but I was curious.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] mysql_fetch_object

[Bob Lockie]

On 06/28/2004 12:07 PM Larry E.Ullman spoke:
I'm using $row = mysql_fetch_object and I can reference column names 
by $row->name but how do I reference a pseudo column (that I make, 
like substring( name from 1 ))?

Use an alias in your query:
SELECT column, DATE_FORMAT('%m', date_column) AS d FROM table
...
$row->d
Larry
How does it handle fully qualified conflicting columns, like 'select 
a.name, b.name from a, b'?

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

Re: [PHP-DB] mysql_fetch_object

[Bob Lockie]

On 06/28/2004 12:07 PM Larry E.Ullman spoke:
I'm using $row = mysql_fetch_object and I can reference column names 
by $row->name but how do I reference a pseudo column (that I make, 
like substring( name from 1 ))?

Use an alias in your query:
SELECT column, DATE_FORMAT('%m', date_column) AS d FROM table
...
$row->d
Larry
Great, thanks.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] mysql_fetch_object

[Bob Lockie]

I'm using $row = mysql_fetch_object and I can reference column names by 
$row->name but how do I reference a pseudo column (that I make, like 
substring( name from 1 ))?

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

[PHP-DB] Re: [PHP] update count

[Bob Lockie]

On 06/16/04 09:53 John Nichel spoke:
Bob Lockie wrote:
What is the best way to only do an update if it going to update only 
one row?
I want to protect my code so that it won't accidentally update more 
than one row.
I can do a select first but there must be an easier way. :-)

UPDATE thisDB.thisTable SET thisTable.thisColumn = 'thisValue' WHERE 
thisTable.uniqueColumn = 'someUniqueValue'
Ok, a primary key is unique, right?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Strange behaviour of Mysql_fetch_array + Submit button

[Bob Gervais]

Hi,

Hopefully I submit this to the correct list.
 
I'm completely new to PHP and am trying to get some sort of shopping
cart working based on an example I found on the WWW.
I have an include page which contains all cart functions (add to cart
function displayed below), and a main page in which I display a form to
show all products with a submit button to add the selected item +
quantity to a cart db-table.
 
The product display + textbox+ submit button work fine, but when I click
the submit button the product just above the one selected is added to
the cart table in the database together with the quantity given in the
correct textfield (I hope you can visualise what I mean).
 
I have added the ADD TO CART code and the main page code below and hope
anyone can help me solve this mystery (which it is to me anyway, but
hopefully not for you guys).
 
Thanx,
 
Bob
 

**
 
/* function code */
 
function add_item($table, $session, $product, $quantity) { 
$qty = $this->check_item($table, $session, $product); 
if($qty == 0) { 
$query = "INSERT INTO $table (session, product,
quantity) VALUES "; 
$query .= "('$session', '$product', '$quantity') "; 
mysql_query($query); 
} else { 
$quantity += $qty; 
$query = "UPDATE $table SET quantity='$quantity' WHERE
session='$session' AND "; 
$query .= "product='$product' "; 
mysql_query($query); 
} 
} 

/* End function code */
 

**
 
/* The php code in the page */
 
Error performing query.".mysql_error()."");
exit();
   };
   $counter = 1;
   while ($row = mysql_fetch_array($Query_result) )
   {
$selected_product = $row["product"];
$selected_quantity_available = $row["quantity"];
$selected_category = $row["category"];
$selected_description = $row["description"];
$selected_price = $row["price"];
echo "";
echo ("Product : ".$selected_product." Quantity :
".$selected_quantity_available." Description : ".
$selected_description." Category : ".$selected_category." Price
: ".$selected_price." EUR ");
echo "";
echo "";
echo "";
echo "Session variable value = $session ...";
echo "";
echo ("Selected product: ".$row["product"]);
echo "";
echo "";
echo "";
$counter++;
if ($Submitname == "Submit$counter")
{
 echo ($row["product"]);
 echo "";
 echo ("Quantity = ".$selected_quantity);
 echo "";
 $cart->add_item("shopping", $session, $row["product"],
$selected_quantity);
};
   };
?> 

/* End page php code */
 

**





Bob GERVAIS
MIS Manager 
XRT, Think Financial Value Chain 
Tel : +32-3-206 92 11 
Fax : +32-3-206 92 00
Tel direct: +32-3-727 29 46 
<mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > 
<http://www.xrt.com <http://www.xrt.com/> >

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

[PHP-DB] stupid mySQL connect \ pconnect problems - pls help

[BOb Pardoe]

I am running the latest Apache2 and PHP 4.3.3 and mySQL 4...

I have installed php as a module in apache.

I cannot connect to the locally installed mySQL database.

It complains that the server cannot be found 

I am doing the following

$Conn = mysql_pconnect($Server, $User, $Pwd);

I have tried setting $Server to be localhost, localhost:3306, the machine
name, 127.0.0.1, all with the same results

I have Norton AntiVirus and Internet Security 2003 installed, could this be
the problem, if so what should I change ?

I have had this all working (about 1 year ago) on older versions without
problems, but it just does not want to work now.

Any ideas please

Thanks

BOb

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

Re: [PHP-DB] storing serialized() data in the db -> can it be queried on?

[Bob Hall]

On Mon, Aug 18, 2003 at 09:39:43AM -0400, Matt Babineau wrote:
> Hi All-
> 
> I don't think that this is possible but maybe through some custom
> function perhaps? I am storing data in a database, and it is an Array
> (in PHP) so I serialized it for storage. I want to be able to query that
> data and be able to do something like WHERE thisField = 'john'. the
> field 'thisField' though would need to be pointing to the first element
> in the Array, which I don't think is possible. Has anyone run into this
> before?

Unless there's a really powerful overriding reason, the best solution is 
to have a seperate table for the array, with a foreign key that points 
back to the record in the original table. That will make retrieving data 
a lot easier; e.g. you can do a query with a join and 
"WHERE thisField = 'john'".

Bob Hall

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

Re: [PHP-DB] Is the query I need even possible?

[Bob Hall]

On Wed, Mar 19, 2003 at 07:37:46PM -0500, Mike Mannakee wrote:
> I have a table that looks like this:
> 
> page_idvisitor_idtimestamp
> ...
> 13   20030101060522
> 58   20030101060512
> 26   20030101060634
> 73   20030101060651
> 35   20030101060612  --> Earliest
> record with this visitor id
> 85   20030101060738
> 21  5   20030101060759
> 15   20030101060810
> 75   20030101060827 --> Last record
> with this visitor id
> 510 20030101060944
> 16   20030101061002
> ... etc
> 
> 
> What I want to select is the PAGE_ID of the EARLIEST record and the PAGE_ID
> of the LAST record, for each distinct visitor_id.  I cannot figure out how
> to do this without multiple, very slow sql calls.

This is pure vi code. (Runs fine in vi; haven't tried it in MySQL.)
If the combination of page_id and visitor_id is a key, then this 
should work. If page_id and visitor_id are not a key (unique identifier), 
then use the table's primary key instead. If you don't have a primary 
key column, consider adding an autonumber primary key and using that. 
The point is that the column(s) in the GROUP BY clause have to uniquely 
identify the row in the first table. 

SELECT v1.page_id, v1.visitor_id, v1.timestamp 
FROM visit v1, visit v2
WHERE vi.visitor_id = v2.visitor_id
GROUP BY v1.page_id, v1.visitor_id
HAVING Max(v1.timestamp) = Max(v2.timestamp);

Substitute Min() for Max() to get the earliest visit. 

Bob Hall

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

Re: [PHP-DB] group by get last record

[Bob Hall]

On Sun, Mar 16, 2003 at 08:02:02AM +, Daniel Harik wrote:
> Hello,
> 
> Guys i try to join to tables
> 
> slides:
> id
> userid
> file
> moment
> 
> users
> id
> username
> 
> As there few slids per user and i want to get only last one, i use following 
> sql query, but it fetches me first slide. How can i make it fetch last one 
> please?
> 
>  SELECT slides.file, slides.moment, users.id, users.username FROM slides, 
> users where users.id=slides.userid GROUP BY users.id desc 

This isn't a PHP question. This relates to the DBMS. You should specify 
which DBMS you are using. However, MySQL is the only DBMS I know of that 
will let you run that query, so I'll assume that you're using MySQL. I'll 
also assume that the Moment column is a time. If it is not, replace 
Moment with whichever column identifies the last slide.

SELECT u1.UserName, u1.UserId, s1.File, s1.Moment
FROM Users u1, Users u2, Slides s1, Slides s2
WHERE u1.UserId = s1.UserId
AND u2.UserId = s2.UserId
AND u1.UserId = u2.UserId
GROUP BY u1.UserId, s1.SlideId
HAVING Max(s1.Moment) = Max(s2.Moment);

BTW, you can't assume that your original statement will always return the 
first slide. People who have tested GROUP BY statements say that the 
value returned from columns with no aggregate function is somewhat 
random.

Bob Hall

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

Re: [PHP-DB] problem with postgres

[bob parker]

On Sun, 10 Nov 2002 04:12, you wrote:
> Hi there,
>
> I got this fustrating problem with postgresql & php can any one help???
>
> my postgresql installation is working fine with my perl application.  My
> php is working fine... however, my php don't seems to be able to connect to
> the postgresql.  I always get the unable to connect error... anyone knows
> what's wrong???
>
>
> I'm using postgresql 7.2.2, php 4.2 and redhat 8.0.  I've added support for
> pgsql and I've confirm that pqsql.so is loaded.

I had similar problems a while ago using Mandrake and connnecting via 
localhost. On my setup the user from php was apache. IIRC I ad to create the 
postgres user 'apache' and provide the necessary permissions on all tables 
etc.

HTH 
Bob

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

Re: [PHP-DB] forum test

[Bob Lockie]

Seabird wrote:


I'll look into it, thanx,

can you access the jump-menu?? Forum should be all the way in the bottom.
 

The jump menu doesn't work.
I think you nad a 'name' field for each 

Jacco
--
http://seabird.jmtech.ca

Attitude is Everything!
But Remember, Attitudes are Contagious!
Is Yours worth Catching
"Mignon Hunter" <[EMAIL PROTECTED]> wrote in message
news:1036610707.1915.12.camel@;joboo.tic.toshiba.com...
 

I'm using Mozilla.  I dont see the word 'forum'

I guess your onChange="loadIframeJumpMenu doesnt work ? Or the type of
javascript your using isnt supported by Mozilla.

BTW the nedstat failed to connect on first try.



On Wed, 2002-11-06 at 12:25, Seabird wrote:
   

Hi everyone, I just uploaded my forum and would like to have it field
 

tested
 

by everyone. It'll prob still have some bugs in there.
http://seabird.jmtech.ca
go to website and then click on forum. please ignore the login in the
 

left
 

top for now,

Jacco


--
http://seabird.jmtech.ca

Attitude is Everything!
But Remember, Attitudes are Contagious!
Is Yours worth Catching



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

 

   




 



--


From Mozilla and GNU/Linux




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

Re: [PHP-DB] Re: persistant database connections

[Bob Lockie]

Richard Allsebrook wrote:


There's a whole world of difference between 'rendered' and 'processed

- the page is processed top to bottom (baring any branching in your php)
- and generally rendered from top to bottom - some parts being rendered
after as the missing components (images etc) are loaded.


Thanks.




From Mozilla and GNU/Linux




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

[PHP-DB] persistant database connections

[Bob Lockie]

I'm using mysql_pconnect(...) at the beginning of a PHP page.
Can I safely use mysql_select_db(...) later on the page?
I thought I read somewhere that you can't rely on HTML to be rendered
from top to bottom.
Can I rely on PHP to be interpreted from top to bottom?



From Mozilla and GNU/Linux




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

Re: [PHP-DB] error messages to variables

[Bob Lockie]

Jason Wong wrote:


On Monday 04 November 2002 12:10, Bob Lockie wrote:
 

"You can suppress the error message on failure by prepending a @
<http://www.php.net/manual/en/language.operators.errorcontrol.php> to
the function name.".
How can you get the error string into a local variable?
   


Look at the "track_errors" setting in php.ini.

 

Thanks.

--


From Mozilla and GNU/Linux




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

Re: [PHP-DB] Connecting to DB on separate server?

[Bob Lockie]

Cassy M Rowe wrote:


I have a php page on server #1 trying to access a database on server 
#2.  What do I put in the following blanks:

$hostname: ___ (have tried IP, http, localhost, nothing works)
$username: blah
$password: blah
$database: _

$link=mysql_connect($hostname, $user, $pass) or die("Failure to 
communicate with database");

$close=mysql_select_db($database, $link);



All I get is the "Failure to communicate."  message.

Server #1: ellerweb.eller.arizona.edu
Server #2: datamonster.sbs.arizona.edu
$database is cassy, but do I need to add anything to that?

I've tried asking two people who are really good at this and they have 
no answers.

THANK YOU IN ADVANCE!

Cassy Rowe

Maybe you have to specify the port of the database on the remote machine.
Maybe the MySQL daemon is not running?

--


From Mozilla and GNU/Linux




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

[PHP-DB] error messages to variables

[Bob Lockie]

"You can suppress the error message on failure by prepending a @ 
 to 
the function name.".
How can you get the error string into a local variable?



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

Re: [PHP-DB] Dropdown list question...

[Bob Lockie]

>   I am populating a dropdown list from a database.  This is working
>fine with one minor glitch...  For some reason, the first entry in the
>database does not seem to be getting populated into the list.  I do not see
>anything wrong, and was hoping that someone else might be able to spot a
>problem.  Here is the code that generates the dropdown list:
>
>$query_systems = "SELECT Name FROM systems";
>$systems = mysql_query($query_systems, $Test) or die(mysql_error());
>$row_systems = mysql_fetch_assoc($systems);
>$totalRows_systems = mysql_num_rows($systems);
>$sys_list = "\n";
>$sys_list .= "System Name\n";
>$sys_list .= "---\n";
>while($name = mysql_fetch_row($systems)) {
>  $sys_list .= "$name[0]\n";
>}
>$sys_list .= "\n";


mysql_fetch_assoc (http://www.php.net/manual/en/function.mysql-fetch-assoc.php) and 
mysql_fetch_row (http://www.php.net/manual/en/function.mysql-fetch-row.php)
both return a row.




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

RE: [PHP-DB] Populating multi-select list from mysql

[bob]

Thanks for all the help. I got it working using the following code from
Martin.

Rob.


$sql = "SELECT category FROM categories";

$result = mysql_query($sql, $connection);

echo "\n";

while ($row = mysql_fetch_array($result)) {
   $id = $row['id'];
   $category = $row['category'];

   echo "$category\n";

   }

   echo "\n\n";
?>

-Original Message-
From: Martin Clifford [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 21, 2002 12:20 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Populating multi-select list from mysql


Whenever you are having trouble with your PHP pages, always echo!  By doing
this, you can see whether or not the query is formatted correctly, whether
or not the variables are containing the values you think they are.  It's
incredibly helpful, no matter what the situation.

Martin

>>> "Ryan Jameson (USA)" <[EMAIL PROTECTED]> 06/21/02 12:10PM >>>
You're logic seems to make sense. The output (page source) you are getting
would help. :-)
<>< Ryan

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Friday, June 21, 2002 10:06 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Populating multi-select list from mysql




Hi all,

I'm having problems getting my multi-select list populated from a mysql
table. There is a table called categories with 2 columns (id and category).
I want to get all the items (category) and list them in the multi-select
list box.

This is the code I have so far:

$sql = "SELECT category FROM categories";

$result = mysql_query($sql, $connection);

//Try This instead of the while statement
foreach($result as $row) {
echo '' . $row['category'];
}
echo '';





while ($row = mysql_fetch_array($result)) {
   $id = $row['id'];
   $category = $row['category'];
   $category_list .= "$category";
   }
?>




  >





Now, if I echo $category_list to the page I can see I have the correct data,
I just can't get it into the list box. I've seem to hit a wall on any
different ways to try and make this work. What am I doing wrong?

Thanks.

Rob.


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

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



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



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

[PHP-DB] Populating multi-select list from mysql

[bob]

Hi all,

I'm having problems getting my multi-select list populated from a mysql
table. There is a table called categories with 2 columns (id and category).
I want to get all the items (category) and list them in the multi-select
list box.

This is the code I have so far:

$sql = "SELECT category FROM categories";

$result = mysql_query($sql, $connection);

while ($row = mysql_fetch_array($result)) {
   $id = $row['id'];
   $category = $row['category'];
   $category_list .= "$category";
   }
?>




  >





Now, if I echo $category_list to the page I can see I have the correct data,
I just can't get it into the list box. I've seem to hit a wall on any
different ways to try and make this work. What am I doing wrong?

Thanks.

Rob.


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

[PHP-DB] MySQL enum displaying values in Select

[bob]

Hello,

I have a problem I can't seem to overcome. Maybe it's just something simple
but for some reason I am unable to read in values from a mysql db column
(enum) and display the results in an option box.

I can display the values to the screen but I can't seem to get them into the
option box. This is the snippet of code I've been struggling with... what am
I doing wrong? All I get in the option box is one value called 'Array' which
isn't even a value from my enum column.

Please let me know if you need more info.

Thanks for the help.

Rob.



(db connections strings here.)

$sql = "SHOW COLUMNS FROM $table_name LIKE 'categories'";
$result = @mysql_query($sql);
$query_data = mysql_fetch_array($result);

if (eregi("('.*')", $query_data["Type"], $match)) {
   $enum_str = ereg_replace("'", "", $match[1]);
   $enum_options = explode(',', $enum_str);
   }

array_push($enum_options, $query_data);
echo "ENUM options with the default value:";
foreach($enum_options as $value) echo "$value";

?>




http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Re: How to force a new browser page?

[bob parker]

On Wednesday 01 May 2002 08:15, you wrote:
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] says...
>

Thanks for the replies on this topis folks.
For the moment the http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] How to force a new browser page?

[bob parker]

I'm working on an intranet accounting application using php and posgresql.
When I select a reord from a list for editing, I transfer the necessary 
variables using the 'METHOD=POST' and similarly when the update scripts are 
run after the edit. 
It makes no sense to use the back buuton to return to the original list so I 
would prefer to open the other pages in new windows.
How please?
If a web developers list is more appropriate for this question kindly 
recommend one.
Thanks
Bob Parker

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

Re: [PHP-DB] Maximum field length with PHP 4.1.2/MSSQL v7 ?

[bob parker]

Pan,
It looks as if you may be using FORM with method=get.
If so try method=post to get the full data

Bob Parker

On Saturday 27 April 2002 20:35, you wrote:
> From: "Chris MacKenzie" <[EMAIL PROTECTED]>
>
> > I've come across an interesting problem. I'm trying to retrieve some
> > text data (in this case a question up to 400 chars), but each time I
> > perform a select statement I only receive the first 255 chars.
> >
> > What's the go here, is this just another microsoft thing ?
>
> Based on the paucity of data in your problem description I would
> be inclined to trust the machine and ask what makes you think there
> should be more than  'char(255)'  worth of characters to retrieve?
>
> i.e., do you know the db.table.field in question is a column type meant
> to hold the data you expect? You seem to be indicating a consistent
> return of 255 when you ask for 400 - seems to be a clue about the
> field specification.
>
> More data::better answers.
>
> Pan

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

[PHP-DB] Problems on inserting two tables

[bob parker]

Using php & postgresql, I have 2 tables and a sequence as follows:

create sequence serial;

CREATE TABLE glmaster (
id  int default nextval('serial'),
accno   char(6) constraint accno_con not null unique,
reportlevel int,
nametext,
isheaderboolean default 'f',
isdebit boolean not null,
inactiveboolean default 'f',
primary key (id)
);

CREATE TABLE glcheque (
id  int references glmaster,
primary key(id)
);

My problem is this, when inserting to glmaster, at times I want to also 
insert the same value of id into glcheque. The values originate in a
form generated using php.
My first attempt was to insert all values except id into glmaster then 
followed by (in php):

$sql = select id as glid from glmaster where accno = $glaccno;
pg_exec($dbcon, $sql);

then
$sql1 = insert into glcheque values ($glid);
etc
that fails because $glid is not initialised.

My next attempt was '$sql = select nextval ('serial') as glid;' etc

Neither does this initialise $glid.

I'm not sure if this is simply a timing problem, or if there is something 
wrong with my logic.

Thanks for any help given
Bob Parker

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

Re: [PHP-DB] How to turn off error display

[bob parker]

On Tuesday 16 April 2002 22:30, you wrote:
> I believe what you are looking for is:
> @pg_fetch_row( $result, $i++ )
>
> But check the manual for @.
>
> =C=
>
> *
> * Cal Evans
> * Techno-Mage
> * http://www.calevans.com
> *
Thanks, I think that's it
Bob Parker

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

[PHP-DB] How to turn off error display

[bob parker]

In the code shown below, everything works ok but  php 
displays an error message when pg_fetch_row tries to
access beyond the last row. How can I turn the error display
off?

// exec query
$result = pg_exec($dbcon, $sql);
$i = 0;
while (($row = pg_fetch_row( $result, $i++ ))) {
// display what we have
// printf/echo stuff
}
pg_close( $dbcon );

Thanks
Bob Parker

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

Re: [PHP-DB] tnsnames.ora + Apache & symbolic links

[Bob]

It is. You can override it in the conf file however.

Later,

Bob

- Original Message -
From: "Florian Clever" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, April 06, 2002 5:33 PM
Subject: [PHP-DB] tnsnames.ora + Apache & symbolic links


> I recently posted a question regarding connecting to a TNS service from
PHP.
>
> I had been getting the error TNS operation timed out from PHP while
> connecting via sqlplus worked perfectly fine.
> If I was not using a TNS alias from PHP but a real connect string such as
>
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DAT
> A=(SID=qa))) it worked perfectly fine as well.
>
> I had a symbolic link pointing from $ORACLE_HOME/network/admin to
> tnsnames.ora file (cvs checked out version). After replacing the symbolic
> link with a hard link it worked fine.
>
> Is it that Apache does not follow the symbolic links outside of its home
for
> security reasons?
>
> Florian
>
> http://fc.clever-soft.com
> http://www.clever-software-solutions.de
>
>
> --
> 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] Stuck on db entry from select box...

[Bob]

Bug report: You'll need to add another = to the if test..

if( $number == '0' ) $sql = "INSERT INTO $table_name( files ) VALUES(
> \"$files\" )";

Dumb mistake..

Bob

- Original Message -
From: "Bob" <[EMAIL PROTECTED]>
To: "jas" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, April 05, 2002 7:44 PM
Subject: Re: [PHP-DB] Stuck on db entry from select box...


> Okay, so I figured out why you might want to use UPDATE instead of SELECT.
> And I also found that no matter what I tried, UPDATE doesn't seem to be
able
> to UPDATE a table if there are no rows in it.. Otherwise it works fine..
So
> I found that I had to put an if test in that would check to see if the
table
> was empty and assign a different value to $sql than if the table wasn't
> empty. This is what I ended up with:
>
> 
> if( !$command ) main();
> if( $command == "goForIt" ) doTheDeed( $files );
> exit;
>
> function main()
> {
> $dir_name = "/home/httpd/html/database/images";
> $dir = opendir($dir_name);
> $file_list .= " ACTION=\"$PHP_SELF?command=goForIt\">
> $file_name";
>  while ($file_name = readdir($dir)) {
>   if (($file_name != ".") && ($file_name !="..")) {
>   $file_list .= " NAME=\"$file_name\">$file_name";
>   }
>  }
>  $file_list .= " VALUE=\"select\">";
>  closedir($dir);
>
> echo "$file_list";
> }
>
> function doTheDeed( $files )
> {
> $db_name = "phpTemp";
> $table_name = "fileList";
> $connection = mysql_connect("localhost", "oops", "didanyonesee") or die
> ("Could not connect to database.  Please try again later.");
> $db = mysql_select_db("$db_name",$connection) or die ("Could not select
> database table. Database said: " . mysql_error() );
> echo( "Executing Query. You submitted $files to
> the database.\n" );
> $getdata = mysql_query( "SELECT * FROM fileList" );
> $number = mysql_num_rows( $getdata );
> if( $number = '0' ) $sql = "INSERT INTO $table_name( files ) VALUES(
> \"$files\" )";
> else $sql = "UPDATE $table_name SET files = \"$files\"";
> $result = mysql_query($sql, $connection);
> $error = mysql_error();
> if( eregi( "uplicate", $error ) ) echo( "Sorry, $files is already in the
> database.\n" );
> else echo( "$files added to database successfully.\n" );
> echo( "Add another file?" );
> }
>
> ?>
>
> Later,
>
> Bob
>
> - Original Message -
> From: "Bob" <[EMAIL PROTECTED]>
> To: "jas" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Friday, April 05, 2002 5:43 PM
> Subject: RE: [PHP-DB] Stuck on db entry from select box...
>
>
> > I got it to work like this.. I don't know if you didn't want to use
INSERT
> > or something but it sticks the filename in the database table for me..
> >
> >  > $dir_name = "/home/httpd/html/database/images";
> > $dir = opendir($dir_name);
> > $file_list .= "
> > $file_name";
> >  while ($file_name = readdir($dir)) {
> >   if (($file_name != ".") && ($file_name !="..")) {
> >   $file_list .= " > NAME=\"$file_name\">$file_name";
> >   }
> >  }
> >  $file_list .= " > VALUE=\"select\">";
> >  closedir($dir);
> > ?>
> > within the page... I echo the results like so:
> > 
> > so far so good, now on the index_done.php3 my code is put into a require
> > statement and the required file code is as follows...
> >  > $db_name = "phpTemp";
> > $table_name = "fileList";
> > $connection = mysql_connect("localhost", "notme", "getyourown") or die
> > ("Could not connect to database.  Please try again later.");
> > $db = mysql_select_db("$db_name",$connection) or die ("Could not select
> > database table. Database said: " . mysql_error() );
> > $sql = "INSERT INTO $table_name( files ) VALUES( \"$files\" )";
> > //$sql = "UPDATE $table_name SET files = \"$files\"";
> > $result = mysql_query($sql, $connection) or die ("Could not execute
query.
> > Database said: " .  mysql_error() );
> > ?>
> >
> > I changed the names of the database and table, stuff like that..
> >
> >
> > Personally, I 

Re: [PHP-DB] Stuck on db entry from select box...

[Bob]

Okay, so I figured out why you might want to use UPDATE instead of SELECT.
And I also found that no matter what I tried, UPDATE doesn't seem to be able
to UPDATE a table if there are no rows in it.. Otherwise it works fine.. So
I found that I had to put an if test in that would check to see if the table
was empty and assign a different value to $sql than if the table wasn't
empty. This is what I ended up with:


$file_name";
 while ($file_name = readdir($dir)) {
  if (($file_name != ".") && ($file_name !="..")) {
  $file_list .= "$file_name";
  }
 }
 $file_list .= "";
 closedir($dir);

echo "$file_list";
}

function doTheDeed( $files )
{
$db_name = "phpTemp";
$table_name = "fileList";
$connection = mysql_connect("localhost", "root", "sinister") or die
("Could not connect to database.  Please try again later.");
$db = mysql_select_db("$db_name",$connection) or die ("Could not select
database table. Database said: " . mysql_error() );
echo( "Executing Query. You submitted $files to
the database.\n" );
$getdata = mysql_query( "SELECT * FROM fileList" );
$number = mysql_num_rows( $getdata );
if( $number = '0' ) $sql = "INSERT INTO $table_name( files ) VALUES(
\"$files\" )";
else $sql = "UPDATE $table_name SET files = \"$files\"";
$result = mysql_query($sql, $connection);
$error = mysql_error();
if( eregi( "uplicate", $error ) ) echo( "Sorry, $files is already in the
database.\n" );
else echo( "$files added to database successfully.\n" );
echo( "Add another file?" );
}

?>

Later,

Bob

- Original Message -
From: "Bob" <[EMAIL PROTECTED]>
To: "jas" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, April 05, 2002 5:43 PM
Subject: RE: [PHP-DB] Stuck on db entry from select box...


> I got it to work like this.. I don't know if you didn't want to use INSERT
> or something but it sticks the filename in the database table for me..
>
>  $dir_name = "/home/httpd/html/database/images";
> $dir = opendir($dir_name);
> $file_list .= "
> $file_name";
>  while ($file_name = readdir($dir)) {
>   if (($file_name != ".") && ($file_name !="..")) {
>   $file_list .= " NAME=\"$file_name\">$file_name";
>   }
>  }
>  $file_list .= " VALUE=\"select\">";
>  closedir($dir);
> ?>
> within the page... I echo the results like so:
> 
> so far so good, now on the index_done.php3 my code is put into a require
> statement and the required file code is as follows...
>  $db_name = "phpTemp";
> $table_name = "fileList";
> $connection = mysql_connect("localhost", "notme", "getyourown") or die
> ("Could not connect to database.  Please try again later.");
> $db = mysql_select_db("$db_name",$connection) or die ("Could not select
> database table. Database said: " . mysql_error() );
> $sql = "INSERT INTO $table_name( files ) VALUES( \"$files\" )";
> //$sql = "UPDATE $table_name SET files = \"$files\"";
> $result = mysql_query($sql, $connection) or die ("Could not execute query.
> Database said: " .  mysql_error() );
> ?>
>
> I changed the names of the database and table, stuff like that..
>
>
> Personally, I would do it like this:
>
> 
> if( !$command ) main();
> if( $command == "goForIt" ) doTheDeed( $files );
> exit;
>
> function main()
> {
> $dir_name = "/home/httpd/html/database/images";
> $dir = opendir($dir_name);
> $file_list .= " ACTION=\"$PHP_SELF?command=goForIt\">
> $file_name";
>  while ($file_name = readdir($dir)) {
>   if (($file_name != ".") && ($file_name !="..")) {
>   $file_list .= " NAME=\"$file_name\">$file_name";
>   }
>  }
>  $file_list .= " VALUE=\"select\">";
>  closedir($dir);
>
> echo "$file_list";
> }
>
> function doTheDeed( $files )
> {
> $db_name = "phpTemp";
> $table_name = "fileList";
> $connection = mysql_connect("localhost", "notme", "getyourown") or die
> ("Could not connect to database.  Please try again later.");
> $db = mysql_select_db("$db_name",$connection) or die ("Could not select
> database table. Database said: " . mysql_error() );
> $sql = "INSERT INTO $table_name( files ) VALUES( \"$files\" )";
> //$sql = "UPDATE $table_name SET files = \"$files\"";
> $result = mysql_query($sql, $connection);
> $error = mysql_error();
> if( eregi( "uplicate", $error ) ) echo( "Sorry, $files is already in the
> database." );
> else echo( "$files added to database successfully." );
> echo( "Add another file?" );
> }
>
> ?>
>
> But it is your project...
> Later,
>
> Bob Weaver
>


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

RE: [PHP-DB] Stuck on db entry from select box...

[Bob]

I got it to work like this.. I don't know if you didn't want to use INSERT
or something but it sticks the filename in the database table for me..


$file_name";
 while ($file_name = readdir($dir)) {
  if (($file_name != ".") && ($file_name !="..")) {
  $file_list .= "$file_name";
  }
 }
 $file_list .= "";
 closedir($dir);
?>
within the page... I echo the results like so:

so far so good, now on the index_done.php3 my code is put into a require
statement and the required file code is as follows...


I changed the names of the database and table, stuff like that..


Personally, I would do it like this:


$file_name";
 while ($file_name = readdir($dir)) {
  if (($file_name != ".") && ($file_name !="..")) {
  $file_list .= "$file_name";
  }
 }
 $file_list .= "";
 closedir($dir);

echo "$file_list";
}

function doTheDeed( $files )
{
$db_name = "phpTemp";
$table_name = "fileList";
$connection = mysql_connect("localhost", "notme", "getyourown") or die
("Could not connect to database.  Please try again later.");
$db = mysql_select_db("$db_name",$connection) or die ("Could not select
database table. Database said: " . mysql_error() );
$sql = "INSERT INTO $table_name( files ) VALUES( \"$files\" )";
//$sql = "UPDATE $table_name SET files = \"$files\"";
$result = mysql_query($sql, $connection);
$error = mysql_error();
if( eregi( "uplicate", $error ) ) echo( "Sorry, $files is already in the
database." );
else echo( "$files added to database successfully." );
echo( "Add another file?" );
}

?>

But it is your project...
Later,

Bob Weaver


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

Re: [PHP-DB] saving details problem (big)

[Bob]

\n" );
echo( "\n" );
echo( "\n" );
echo( "\n" );
echo( "\n" );
echo( "\n" );
}

function postcodeLookup()
{
global $pcdata, $youknowit, $maybe;

$result = $database->query( "INSERT INTO victims( idNumber, firstName,
lastName, phone, emailAddress, whateverElse ) VALUES( '',
\"$pcdata[firstName]\", \"$pcdata[lastName]\", \"$pcdata[phone]\",
\"$pcdata[emailAddress]\", \"$pcdata[whateverElse]\" )" );
if( !$result ) echo( "Whoa.. Something's not working right here.." );

//put code here to go do the postcode lookup

showResults();
}

?>

Just an idea.. Hope it will help or jog something..

Bob Weaver

> I am making use of AFD postcode lookup application.
> What it dose is ask for your postcode and returns your address.
> You may have seen this around the web..
> Well my problem is that I have added 3 extra fields
> Name, email, phone number
> And I need to save this info.
> I have tried sessions and cookies but have just noticed
>
> That the form action is a cgi file on the afd server. Thus this is
> stopping me Saving info.
> Any ideas on how I can save the info.
> I am in a back to the wall situation so any pointer and guidance is
>
> Most appreciated.

> P.S Had it work all fine and dandy when I added the extra info after
> postcode lookup but customer insisted he wants it this way round.
> Customers...
> (cant live with them, cant live without them...)
> Dave Carrera
>
> Php Developer
>
> http://davecarrera.freelancers.net
>
> http://www.davecarrera.com



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

[PHP-DB] Re: how to question...

[Bob Klotz]

Hi Jas,

if I understood your question right, you can solve your problem best with
something like the following:



Bob

"Jas" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Ok here is what I would like to accomplish, I am sure someone has done
> something like this but I need an example to go from since I am still not
> able to write php from scratch...
> I need to write a function to count the contents of a directory, like the
> file names and place them into an array so I can use a select box to
select
> the file path in which I would like to save to database, like I said I
don't
> want someone to do it for me, I just need a resource such as a tutorial or
> example to run with. Thanks a ton,
> Jas
>
>



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

Re: [PHP-DB] Editing/Updating Data with Forms

[Bob]

Sounds like you're more in need of a consultant than a mailing list..

Bob
- Original Message -
From: "Evans, Josh" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 04, 2002 8:30 AM
Subject: [PHP-DB] Editing/Updating Data with Forms


> Can anyone help me with editing/updating data in a table with using a
form?
>
> Josh Evans
> ACS Helpdesk
> [EMAIL PROTECTED]
>
>
> --
> 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] Time table was updated?

[Bob Holm]

Is there a function available to find out what time a table in a mySQL
database was updated?  If you can help please mail me at
[EMAIL PROTECTED]   Thanks



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

Re: [PHP-DB] How can recommend a book for SQL and DB design

[Bob Hall]

On Tue, Jan 15, 2002 at 11:11:25PM +0100, Andy wrote:
> Hi,
> 
> I am searching for a good book on how to design a db for web apps.
> Normalisation rools, performancejust describing how to do it on a
> proffesional way.
> 
> The second one, I am searching for is a book about SQL for MySQL. Any good
> books on the market?
> 
> Thanx for the recommendation

I've got about four books on MySQL, including one on using MySQL for 
web development, but the book I would recommend for both your needs is 
'MySQL' by Paul DuBois. The book is a little out of date and you will 
need to check the on line manual to see what has changed, especially 
for SQL. But it covers the basics of SQL and performance better than 
anything else. Almost all your beginning questions will be answered 
there. 

For a more advanced treatment of performance, Jeremy's book sounds 
like the best bet, when it comes out.

Bob Hall
-- 
Know thyself? Absurd direction!
Bubbles bear no introspection.-Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] PHP/Apache not totally closing MySQL connections

[Bob Maple]

> >Proto Recv-Q Send-Q Local Address
> >tcp1  0 burner.com:4848 burner.com:mysqlCLOSE
>
> [munge-alert!]

Hmm, looks like my pastes got whacked.  Oops.

> nope, if you leave it going for awhile nothing out of the ordinary
happens,
> the socket will be reused (when it's time is up).
>
> the reason it doesn't "go away" until Apache is closed is that this is
apache's
> socket, and not MySQL's, as the forreign address is the :mysql.

OK, thanks -- I did some rapid refreshes of a page and indeed, the 'CLOSED'
state sockets ultimately are being recycled.  I guess I just wasn't getting
the same httpd process that had them open before, because at one point I had
about 5 of these sockets hanging around while new connections continued to
appear.


 :  Bob Maple  (Brazilian)  : When love is gone, there's always justice.  :
 :  bobm at burner dot com  : And when justice is gone, there's always:
 :  : force.  And when force is gone, there's :
 : http: burner.com : always Mom.  Hi, Mom! - Laurie Anderson :



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] PHP/Apache not totally closing MySQL connections

[Bob Maple]

I've been through the bug database a couple of times, and scanned through
the list archives before joining, but haven't exactly found anything that
matches what is happening in my case.

First, dirty details: I am running MySQL 3.23.33, Apache 1.3.19, and PHP
4.0.6 under an RH system on kernel 2.2.19.  PHP running as a module in
Apache.

I am having a little quirk with persistant connections with MySQL.  The
connections themselves work;  However, after a while when the connections
time out, they don't seem to be closing all the way.  For instance, right
now if I do a netstat, among some other connections I get:

Proto Recv-Q Send-Q Local Address
tcp1  0 burner.com:4848 burner.com:mysqlCLOSE

This has been there and been in "CLOSE" state all day.  It was there
yesterday. It will be there tomorrow.  MySQL has terminated the connection
as far as it cares (if I "mysqladmin processlist", no connections show up.)
Indeed, if I shut down the MySQL server, the socket remains.  Only if I shut
down Apache will it ever go away.

So, if left going for awhile, tons of these dead sockets will pile up.

Before I post a bug report, anyone have any ideas?


 :  Bob Maple  (Brazilian)  : When love is gone, there's always justice.  :
 :  bobm at burner dot com  : And when justice is gone, there's always:
 :  : force.  And when force is gone, there's :
 : http: burner.com : always Mom.  Hi, Mom! - Laurie Anderson :



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Use of Like

[Bob Hall]

>Can anyone tell me a way to simulate the command "LIKE" in mySQL query¥s,
>
>Thanks in advance,
>
>Gabriel.

Sir, you can use either LIKE or REGEXP. If you are coming to MySQL 
from an MS product, use % in place of * when using LIKE. For more 
information, look in the language section of the MySQL online manual.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] ORDER BY in MYSQL ? 8(

[Bob Hall]

>question:
>
>Do any of you knwo how to correctly sort output from a query that contains
>"Umlaut" like " Ä Ö Ü" etc. so that it looks like this:
>
>A
>Ä
>B
>C
>D
>
>O
>Ö
>P
>R
>
>
>and not like MYSQL does it now with the ORDER BY function, which puts all
>the "Umlaut" at the end like this:
>
>A
>B
>C
>D
>...
>O
>P
>R
>...
>Z
>Ä
>Ö
>
>thx in advance for any suggestions. I didn't find anything in the
>manualunder the ORDER BY documentation, but maybe myasql can configure the
>kind of sort in prefs or such? =)
>
>Jens Nedal
>
>
>So long and thx for the fish!

Sir, you may have to change the character set. I think the default 
character set  causes sorts to be correct for Swedish, which puts the 
funny looking vowels at the end. I know that I have to use the Danish 
character set to get the correct sort for Norwegian. Check the manual 
for instructions on changing character sets. It's been a while since 
I've done it, and I no longer remember how.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] SELECT question

[Bob Hall]

>Hi All,
>
>I'm building a standard shopping cart style e-commerce site using 
>PHP and MySQL running on Apache.
>
>I store my users' cart info in this table:
>
>++--+--+-+-+---+ |
>| Field  | Type | Null | Key | Default | Extra | +
>++--+--+-+-+---+ |
>| custId | int(11)  |  | | 0   |   | |
>| itemId | int(11)  | YES  | | NULL|   | |
>| qty| int(11)  | YES  | | NULL|   | |
>| totalPrice | float(10,2)  | YES  | | NULL|   | |
>| dateAdded  | timestamp(6) | YES  | | NULL|   | +
>++--+--+-+-+---+
>
>I currently use this statement to display a user's cart contents:
>
>SELECT items.itemId, description, link, qty, price FROM carts, items 
>WHERE carts.custId = '$custId' AND items.itemId = carts.itemId
>
>If a user happens to add the same item to their cart more than once, 
>this statement displays the item more then once.  Is there a way I 
>can augment the select statement above so I can group multiple 
>instances of the same product into a single line, but still get a 
>sum of the quantities so the single lines reflects the total 
>quantity of all the instances.  So for example, if add 2 of itemId 1 
>and then add 3 more of itemId 1 my cart will display itemId 1 two 
>times ... once with a qty of 2 and once with a qty of 3.  Instead, I 
>would like it to display one time with a qty of 5.
>
>Make sense?  I'm sure I could hack around this, but I'd like to know 
>if it is doable in a single select statement.
>
>Thanks!
>
>Nick

Sir, use GROUP BY and an aggregate function.
SELECT items.itemId, description, link, Sum(qty) AS sum_qty, price
FROM carts, items
WHERE carts.custId = '$custId' AND items.itemId = carts.itemId
GROUP BY itemId;

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Help with mysql and php 4.03

[Bob Hall]

>Hi I'm trying to put up some php files I have created that is working
>normaly at php local server with php 4.04 and mysql 3.23.36
>
>but it won¥t work at the servr where the client will have it
>with php 4.03 and mysql 3.22.32
>
>the thing that won¥t work is when I got a sql query that contains a inner
>join statement
>that wont work with 3.22.32 is there any one that knows if it can¥t handle
>inner join statemaents or are  the loking diffrentlie
>
>the inner join statement that I have is below
>
>$result = mysql_query("SELECT * From city
>inner join state_prov on state_prov.state_id = city.state_id
>inner join area_phone on area_phone.city_id = city.city_id
>inner join phone_nr on phone_nr.phone_id= area_phone.phone_id
>inner join netwrk on netwrk.net_id = area_phone.net_id
>inner join country on country.country_id = state_prov.country_id
>inner join areacode on areacode.area_id = area_phone.area_id
>ORDER BY '$sSort'");
>
>Thanks / Tobbe

Sir, earlier versions of MySQL didn't support the INNER JOIN 
keywords. For 3.22.x, replace the INNER JOINs with commas and move 
the ON conditions to the WHERE clause.

FROM city, state_prov, area_phone, ...
    WHERE state_prov.state_id = city.state_id
   AND area_phone.city_id = city.city_id AND ...

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Select statements - A Quest !!!

[Bob Hall]

>I have two tables namely,
>1. cpkaizen : The following is the desc
>
>Field | Type | Null | Key | Default | Extra |
>++--+--+-+-++
>| kno | int(10) | | PRI | NULL | auto_increment |
>| loginid | varchar(20) | YES | | NULL | |
>| period | varchar(6) | YES | | NULL | |
>| peryear | int(5) | YES | | NULL | |
>| star | char(1) | YES | | N | |
>| name1 | varchar(30) | YES | | NULL | |
>| name2 | varchar(30) | YES | | NULL | |
>| name3 | varchar(30) | YES | | NULL | |
>| name4 | varchar(30) | YES | | NULL | |
>| date | date | YES | | NULL | |
>| problem | varchar(255) | YES | | NULL | |
>| action | varchar(255) | YES | | NULL | |
>| result | varchar(255) | YES | | NULL | |
>| benefit | varchar(255) | YES | | NULL | |
>| evalution_para | varchar(100) | YES | | NULL | |
>| dept | varchar(20) | YES | | NULL | |
>| name0 | varchar(30) | YES | | NULL | |
>
>(* the records in this table are KAIZENS. dept is of that employee 
>i.e either \'ms\' or fin,com,log,epcm,po,ms,hr . *)
>
>2. employee : following is the desc
>
>Field | Type | Null | Key | Default | Extra |
>+---+-+--+-+-+---+
>| edpno | int(10) | YES | | NULL | |
>| name | varchar(40) | | | | |
>| loginid | varchar(20) | YES | | NULL | |
>| superuser | char(1) | YES | | N | |
>| groupno | int(4) | YES | | NULL | |
>| dept | varchar(30) | YES | | NULL | |
>| sub_dept | varchar(30) | YES | | NULL | |
>
>(* dept is common for employees i.e \'cp\' but sub_depts are 
>fin,com,log,epcm,po,ms,hr . So there are more than one employee in 
>each sub_dept *)
>
>Now actually, I want to generate the report :
>
>the output should be :
>
>Department | Total no of employee | Total KAizen | Average

Average what?

>
>(* here under departments should come the above seven mentioned.
>then total no of employees in eeach sub_dept.
>Then total no of KAIZENS for that sub_dept from table cpkaizen
>and
>Average which is Total no of kaizen divide by total no of employees *)
>
>How can I go ahead to get that output table generated.
>
>Thank you,
>Pranot

Sir, from looking at your tables, I can't tell which contains the 
parent records and which contains the child records. I might be able 
to figure it out if know what a kaizen is. You haven't declared any 
primary keys on the employee table, and you didn't say which DBMS 
you're using.

In general, pattern is

    SELECT dept, Count(p.something), Count(c.something_else), Avg(some_column)
FROM parent_table AS p INNER JOIN child_table AS c ON p.id = c.p_id
GROUP BY dept;

The actual syntax depends on your DBMS.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Re: PostgreSQL versus MySQL

[Bob Hall]

>On Mon, 9 Apr 2001, Bob Hall wrote:
>
>  > >  MySQL is providing an SQL frontend to a
>  > >bunch of tables and indices, that is it ... it is up to the programmer to
>  > >handle the "managing of data" part where it revolves around being
>  > >relational ...
>  >
>  > I've developed database apps in which the data was inserted in
>  > batches, which meant that transactions were unnecessary. On the other
>  > hand, the apps needed an RDBMS to handle normalized tables.
>
>Okay, so you start the insert, and one of the records in the batch failed
>to insert ... then what?  You manually rollback the other ones?

Well, with MySQL, you start the batch over again, but use IGNORE in 
the INSERT statement, and MySQL passes over the records that are 
already inserted.

>   A
>"transaction" is effectively a batch ... if one of the batch fails, either
>the programmer has to manually remember and roll everything back, or you
>let the database itself handle it ..

No, a batch isn't very much like a transaction. In OLTP, the DBA 
doesn't initiate a transaction, has no control over it, and can't 
recover lost data. In batch processing, the DBA initiates and 
controls the batch, and any missing data is in the file.

>  > Futhermore, some datawarehousing and web projects involve relational
>  > databases that are inserted and updated in batches at night, making
>  > transactions unnecessary.
>
>See above ... I have an application that loads ACT! data into a database
>every night ... each contact in the system has something like 20-30 fields
>associated with them ... if, for some reason, *one* of those fields fail
>to insert properly, that contact is invalid, and the transaction that its
>wrap'd in automatically rolls back everything I've done since the start of
>the transaction, so that there is no record of that failed contact except
>in my error log file ... no "incomplete" data, no stray data ...

If your DBMS doesn't give you the capability to restart the batch at 
the point where it failed, then that's necessary. I have a hard time 
believing, though, that you can't do that in whatever DBMS you're 
using. I'll concede that transactions are preferable in that 
circumstance, but I also have experience with databases where 
transactions have nothing to offer.

>batch or interactive doesn't matter ... its the data integrity that is
>maintained by using transactions that is key ...
>
>  > I'm not trying to claim that MySQL can handle all types of db
>  > applications. MySQL is a niche product that was never designed to
>  > handle certain types of applications. My point is that whether a DBMS
>  > is relational depends on the structure of the data it deals with.
>  > Whether it needs to support transaction depends on the environment it
>  > operates in. I think that your point is that in an OLTP environment,
>  > lack of transaction support screws up the data to the point that the
>  > database becomes useless. I agree, but not all RDBMSs operate in an
>  > OLTP environment.
>
>No, my point is that in any environment that needs the features of being
>"relational" (data spread across multiple tables, link'd together), IMHO,
>transactions are required in order to maintain data integrity *unless* the
>programmer himself wants to take it upon himself to maintain this data
>integrity in the application layer ...
>
>... if data in table C requires that the data saved to table B was stored,
>then if table B fails, the transaction should fail and the changes to
>table A should be reversed automatically ... *shrug*  By extension, if the
>data to table C fails for whatever reason, the data put to Tables A and
>B should be automatically reversed ...

... or the missing data inserted. How did DBAs handle batch 
processing before there were transactions?

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Re: PostgreSQL versus MySQL

[Bob Hall]

Doug,

There's something wrong here. This is the internet, we're 
disagreeing, but we're not flaming each other. If we keep this up, 
they'll revoke all our software licenses because of our noncompliant 
behavior.

>Hi Bob!
>
>That would make a very interesting study.  Attempting to come up with a
>modern definition of RDBMS.  Of course, it would be only an academic
>exercise...but it would be a fascinating paper if any youngsters reading
>this from a university dorm room or computer lab would like to tackle it.

It's too late. Everyone has their own definition of RDBMS, which they 
aren't going to give up. :)

>Yes, the SQL standard is a moving target.  But so are the likes of COBOL
>and FORTRAN.  So all one can do is attempt to produce a product that at
>least attempts to conform somewhat to some published version of the
>standard.  It is true that no product adheres perfectly to ANSI SQL
>standards.  I believe that that's primarily because, as it exists, SQL is
>an incomplete and underdefined language.  However, some products appear to
>be more in compliance than others.  But I offer up my pet peeve (MySQL's
>bastardization of LIKE and the recent addition of the BINARY keyword
>instead of fixing the problem) as an example of MySQL's blatant
>non-conformance.  There are examples in PostgreSQL's history (which indeed
>has had a turbulent past) of similar things, but they seem to try with
>every release to bring themselves more in conformance with either ANSI's
>published standard or at least the generally accepted implementation of
>what ANSI has published when the standard is unclear.  MySQL just
>complicates an already bizarre language with even more bizarre constructs
>(BINARY is a recent example).

 From Paul's book: "BINARY causes number-to-string conversion."

It may not comply with the letter of the SQL standards, but it 
certainly complies with the spirit; it's completely non-intuitive!

>I would tend to agree with THH that if MySQL is a database management
>system it should offer up more than just their SQL-like front end to a
>bunch of indexed disk files.
>
>I've read their site.  (I like their site and their documentation better
>than PostgreSQL's site and documentation, by the way.)  Their excuse for
>not conforming to SQL standards and for not implementing important features
>is that they would slow it down unnecessarily.  Two things about that:
>
>  * I do not think things like triggers and transactions are unnecessary, and
>
>  * According to recent reports, PostgreSQL is no longer a dog.  Of course,
>this is beta code...but all indications are that it not only performs
>admirably, but it also exhibits quite acceptable performance under load.
>That's something that a roughly similar MySQL installation appears to have
>some problems with, according to the same studies.  (Major reference would
>be SourceForge, but there have been some other reports bandied about.  Note
>that one must take anything resembling a benchmark as an advisory, not
>gospel.  Also note that for every experience or study that favors one
>product over another, there will undoubtedly be another that says the
>opposite.  So I encourage everyone to take all such reports as ADVISORY
>ONLY and make up their own mind.)
>
>Now, PostgreSQL has triggers and transactions and features that MySQL
>specifically omits (and indeed eschews) because they say those features are
>not important and they would slow everything down.  Do the stories we're
>hearing about the new PostgreSQL offer evidence against MySQL's seemingly
>firm stance against triggers and transactions?  I don't know.  I suppose
>time will tell.
>
>If anyone is actually following this conversation (other than us die
>hards), please don't take all of this as MySQL bashing.  That's not what
>I'm trying to do.  I use MySQL myself for certain things.  As a matter of
>fact, I find that you can turn even the a Win32(R)(TM)(C)(BC)(AD) machine
>(which is what I use for my favorite email client, Eudora) into a web
>development powerhouse with the Win32 versions of Apache, PHP, MySQL, The
>Gimp and WinCVS.  This laptop that I'm typing on right now is outfitted as
>described.  I couldn't buy this laptop without Win98 so I am still using
>it.  (Maybe my next laptop won't be so limited?)  Anyway, getting
>PostgreSQL to work on Win32 is a PITA.  PostgreSQL does work on Win32.  I
>know because I got it to work.  But I didn't like it, and stopped using it.
>
>Doug

I will agree with everything thing you say, with the understanding 
that there are many projects for which the functionality missing from 
MySQL isn't necessary. (And with the understandi

Re: [PHP-DB] Re: PostgreSQL versus MySQL

[Bob Hall]

>On Sun, 8 Apr 2001, Bob Hall wrote:
>
>  > Doug,
>  >
>  > You've posted your usual good sense, combined with one statement I
>  > strongly disagree with.
>  >
>  > >One of
>  > >these products is a relational database management system.  The other is a
>  > >quasi-SQL-like-front-end-to-systems-of-indexed-files that has never
>  > >concerned itself with things like standards conformance.
>  >
>  > The implication is that MySQL is not an RDBMS. The only attempt I
>  > know of to define an RDBMS was Codd's, and no DBMS has ever met the
>  > criteria he published in a paper in the late 80s (1986?). Even though
>  > Oracle doesn't meet the criteria of the best known definition (only
>  > definition?) of an RDBMS, we all seem to agree that Oracle is an
>  > RDBMS. An RDBMS is a DBMS designed to manage a relational database,
>  > and a database is relational because it stores data in linked,
>  > normalized tables.
>
>The only thing I question in this is that without transaction support,
>what exactly is MySQL "managing"?

The data in the normalized tables.

>  MySQL is providing an SQL frontend to a
>bunch of tables and indices, that is it ... it is up to the programmer to
>handle the "managing of data" part where it revolves around being
>relational ...

I've developed database apps in which the data was inserted in 
batches, which meant that transactions were unnecessary. On the other 
hand, the apps needed an RDBMS to handle normalized tables.

>
>Also, note, that even today, MySQL does not handle transactions, Berkeley
>DB does, and its purely optional, and per table.  So, effectively, it has
>"transactional tables", its not a "transactional system" ... if you didn't
>define a table for transactions when you created it, you just created your
>weak link ... again, its not MySQL that manages the system, its the
>programmer who has to do it ... *shrug*

Transactions have to do with the environment the DBMS operates in, 
not the type of database. Relational database theory was developed 
when multi-user OSs were still pretty new, and database processing 
was batch processing. In that type of environment, ACIDity isn't an 
issue. In an OLTP environment, even OODBMSs have to deal with 
transactions. At the same time, the lack of transaction support 
doesn't disqualify an OODBMS from being object oriented.  Futhermore, 
some datawarehousing and web projects involve relational databases 
that are inserted and updated in batches at night, making 
transactions unnecessary.

I'm not trying to claim that MySQL can handle all types of db 
applications. MySQL is a niche product that was never designed to 
handle certain types of applications. My point is that whether a DBMS 
is relational depends on the structure of the data it deals with. 
Whether it needs to support transaction depends on the environment it 
operates in. I think that your point is that in an OLTP environment, 
lack of transaction support screws up the data to the point that the 
database becomes useless. I agree, but not all RDBMSs operate in an 
OLTP environment.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Re: PostgreSQL versus MySQL

[Bob Hall]

Doug,

You've posted your usual good sense, combined with one statement I 
strongly disagree with.

>One of
>these products is a relational database management system.  The other is a
>quasi-SQL-like-front-end-to-systems-of-indexed-files that has never
>concerned itself with things like standards conformance.

The implication is that MySQL is not an RDBMS. The only attempt I 
know of to define an RDBMS was Codd's, and no DBMS has ever met the 
criteria he published in a paper in the late 80s (1986?). Even though 
Oracle doesn't meet the criteria of the best known definition (only 
definition?) of an RDBMS, we all seem to agree that Oracle is an 
RDBMS. An RDBMS is a DBMS designed to manage a relational database, 
and a database is relational because it stores data in linked, 
normalized tables. In general, the term RDBMS is used to mean a DBMS 
that can be used to create, modify, and extract data from normalized 
tables. This covers the functionality described in Codd's early 
papers and doesn't exclude all existing DBMSs.

No RDBMS provides a dialect of SQL that conforms perfectly to the 
standard. The MySQL interface uses a dialect that conforms in the 
most important areas. The conformance is close enough that someone 
like me, who refuses to learn non-SQL interfaces because they can 
only be used with the DBMS they were designed for, is perfectly 
comfortable with the MySQL interface. Given the different versions of 
the SQL standard, and the variety of non-standard implementations, it 
seems pretty arbitrary to call one vendor's SQL dialect 'SQL' and 
another's 'quasi-SQL'. And whether the DBMS implements its databases 
as systems of indexed files, or through some other system, looks 
pretty irrelevant from my porch.

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] How to limit a WHILE statement?

[Bob Stone]

Dear PHP Helpers,

What is your recommendation on limiting a while
statement so that it only iterates a maximum of 20
times?

Here is the code snippet:

$result = mysql_query($sql, $db);

while ($myrow = mysql_fetch_array($result)) {

printf("Name: %s
%s %s %s\n", $myrow["salutation"],
$myrow["first_name"], $myrow["mid_name"],
$myrow["user_name"]);

Best regards,

Bob Stone

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/?.refer=text

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] How to eliminate bad fields from a mysql query?

[Bob Stone]

Dear PHP Helpers,

Can you direct me to a solution for this problem:

How can I eliminate bad (blank or incorrect) fields
from a mysql query? In other words, if someone leaves
a form field blank or enters a value that does not
have a matching entry in the database, how can I
eliminate that variable from the query?

The query that I am using now works fine if the user
enters a value in field that exists in the database.
However if a field is left blank or incorrect data is
entered, no results will display on the screen.

Here is the code that I am using:

$result = mysql_query("SELECT salutation, first_name,
mid_name, user_name, cert_deg, job_title, institution,
address_1, address_2, geo_loc, state, zip, country,
phone, fax, e_mail FROM svt_members where first_name
like '$first_name' && user_name like '$user_name' &&
institution like '$institution' && state='$state' &&
country like '$country'", $db);

while ($myrow = mysql_fetch_array($result)) {

printf("Name: %s
%s %s %s\n", $myrow["salutation"],
$myrow["first_name"], $myrow["mid_name"],
$myrow["user_name"]);

printf("Credentials and Degrees: %sJob Title:
%sCompany/Institution:
%sAddress: %s %sCity:
%s  
State/Province: %sCountry: %s   Postal Code:
%sVoice: %s   Fax: %sE-mail:
%s\n",
$myrow["cert_deg"], $myrow["job_title"],
$myrow["institution"], $myrow["address_1"],
$myrow["address_2"], $myrow["geo_loc"],
$myrow["state"], $myrow["country"], $myrow["zip"],
$myrow["phone"], $myrow["fax"], $myrow["e_mail"]);
}

So if (for instance) a user enters data for each field
except institution, how can I get the query to
disregard $institution?

Or if there is a better approach that I should be
using instead of the above, please let me know.

Thank you,

Bob

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/?.refer=text

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: FW: [PHP-DB] SELECT MAX(ID) PLUS 1

[Bob Hall]

Ma'am, I seem to have come late to this thread, and I think you've 
already chosen to go back to the multiple-table schema. I'm just 
posting to say that I think you made the right decision.

If you have data that is structured in tree form, i.e. if you have 
something along the lines of
*
   / \
   * *
/ \
* *
   /
   *
and the number of levels is known ahead of time and can be a fixed 
part of the design, then you should use one table for each level in 
the hierarchy. (Even where the structure is more complicated that a 
tree structure, the principle still applies.) If I understand your 
description properly, this describes your web page design. One type 
of entity links to one or more other types of entity, which in turn 
link to one or more other types, on down the tree to a fixed depth. 
Relational databases were created to handle this type of information 
structure.

If your web page data is structured in this way, then the person who 
told you to use a single table either didn't understand your data 
structure, or doesn't understand database design. My experience is 
that when this principle is ignored, the project is in trouble. 
There's a limit to how much a programmer can do to solve problems 
caused by bad database design, as you have discovered.

Bob Hall

>Can anyone help me on this one
>Lisa
>-Original Message-
>From: Walter [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, March 28, 2001 10:55 AM
>To: Rubanowicz, Lisa
>Subject: Re: [PHP-DB] SELECT MAX(ID) PLUS 1
>
>
>Are tables are to differnet, so you don't have anything on my code
>
>Sorry
>
>- Original Message -
>From: Rubanowicz, Lisa <mailto:[EMAIL PROTECTED]>
>To: 'Walter' <mailto:[EMAIL PROTECTED]>  ; Rubanowicz, Lisa
><mailto:[EMAIL PROTECTED]>
>Sent: Wednesday, March 28, 2001 10:57 AM
>Subject: RE: [PHP-DB] SELECT MAX(ID) PLUS 1
>
>HI Walter,
>Could you please send me an excerpt of your code so I can see is my PHP
>syntax incorrect.
>Thanks Walter
>Lisa
>
>-Original Message-
>From: Walter [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>]
>Sent: Wednesday, March 28, 2001 10:09 AM
>To: Rubanowicz, Lisa
>Subject: Re: [PHP-DB] SELECT MAX(ID) PLUS 1
>
>
>I think that your tables aren't good, my site have to menu's and submenu's
>but i don't have that problem
>
>- Original Message -
>From: Rubanowicz, Lisa <mailto:[EMAIL PROTECTED]>
>To: 'Walter' <mailto:[EMAIL PROTECTED]>
>Sent: Wednesday, March 28, 2001 10:40 AM
>Subject: RE: [PHP-DB] SELECT MAX(ID) PLUS 1
>
>
>I can't use auto_increment on two fields in the same table!!
>pg_id is auto_increment
>Lisa
>
>-Original Message-
>From: Walter [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>]
>Sent: Wednesday, March 28, 2001 9:42 AM
>To: Rubanowicz, Lisa
>Subject: Re: [PHP-DB] SELECT MAX(ID) PLUS 1
>
>
>Why don't you use the auto_increment fucntion in MySQL self?
>
>- Original Message -
>From: Rubanowicz, Lisa <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Wednesday, March 28, 2001 10:17 AM
>Subject: [PHP-DB] SELECT MAX(ID) PLUS 1
>
>
>  > Hi All,
>  > I have been driven maad with this now for about a week and have looked
>
>  > all around for the answer but seem to get sent off in a tangent any time I
>
>  > get close.  My SELECT statement seems to be right when I run it in
>  > MySQLFront but to add 1 in PHP ..
>  >
>  > Here is the story.  I have a small web site running from mySQL.  Initially
>
>I
>  > had it all in different tables then an ASP colleague said I should do it
>it
>  > one table and use SELECT MAX(sub_id) etc.
>  > The site has obviously main navigation links Products, Freight Software,
>  > Small to Medium businesses and News etc.  along the top of the site. But
>for
>  > each of the first three I want "Sub Links" (Products 1, Products 2,
>Products
>  > 3 etc)  So I have a table like so
>  >
>  > pg_id (auto_increment)
>  > page_id (gives the type of page - one of the main six links)
>  > page_name
>  > sub_id (I need this to be 1, 2, 3 etc depending on how many pages they add
>
>  > to each section through a web form)
>  > sub_name (Clickable text for sub link)
>  >
>  > Of course there are many other fields (Title of page, Content of page, IMG
>
>  > for page etc)
>  > My problem is when I do a SELECT MAX(sub_id) AS sub_id_max WHERE page_id =
>
>  > $page_id  I can't seem to get that figure in PHP and add 1 to it
>  > (increment).

[PHP-DB] Copy data from one mysql table to another?

[Bob Stone]

Dear PHP Helpers,

I cannot find any documentation on how to copy the
data from one mysql table to another, i.e. table1,
columnx to table2, columnx.

Can you recommend a way?

Here is the situation.

I have one table called "phone" with two columns. The
first column is called "key_svt_members," the second
is called "phone."

I have a second table called "svt_members." This table
has 17 columns including "key_svt_members" and
"phone." The phone column in this table is currently
empty.

I wish to copy the data that is in phone, phone into
svt_members.

Can you recommend a method?

Thanks in advance,

Bob Stone

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/?.refer=text

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] How to print last row in an array using WHILE?

[Bob Stone]

Dear PHP Helpers,

I have an array created by an mysql select statement.

I have the code to display the "rows" from the select
statement.

Everything works fine except that only n-1 rows from
the array will display.

For example if the array contains four rows only three
will display. If the array contains one row, nothing
displays on the screen.

I understand that a WHILE loop will count down to zero
and then since it becomes false will quit, but how do
I get the last (or first) row to print?

Here is the code:











%s %s %s %s\n",
$myrow["salutation"], $myrow["first_name"],
$myrow["mid_name"], $myrow["user_name"]);

printf("Credentials and Degrees: %s   Job Title:
%sCompany/Institution:
%sAddress: %s %sCity:
%s  
State/Provence: %sCountry: %s   Postal Code:
%sVoice: %s   Fax: %sE-mail:
%s\n",
$myrow["cert_deg"], $myrow["job_title"],
$myrow["institution"], $myrow["address_1"],
$myrow["address_2"], $myrow["geo_loc"],
$myrow["state"], $myrow["country"], $myrow["zip"],
$myrow["phone"], $myrow["fax"], $myrow["e_mail"]);

} 

}
?>



Enter Last Name To Search:  









Thank you very much for your help.

Best regards,

Bob Stone

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/?.refer=text

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Tricky database query involving two tables, much more detail

[Bob Hall]

>At 07:04 PM 2/28/01 -0800, Ron Brogden wrote:
>  >At 09:50 PM 2/28/2001 -0500, you wrote:
>  >>Is there a way to do this in MySQL?  Or do I have to just query 
>everything, and then have the skipping logic be in PHP?  I'd love to 
>encapsulate this in a query.  Would it involve subqueries (something 
>I know that MySQL doesn't directly support)?
>  >
>  >The part that seems to be missing here is that you are note 
>limiting the relationships between table A and table B, you are just 
>slapping them together.  There should probably be a field that you 
>use to lock rows together between the tables:
>  >
>  >select a.foo,b.bar from A left outer join B ON a.foo=b.foo where b.bar=1
>
>Thanks, Ron...
>Actually, I am doing that (I had implied it when I said "on something"):
>select * from A left join B on something
>where B.thing != "1" or B.thing is NULL
>
>The problem, again, is that this gets me things that are in B that 
>don't have a certain attribute...but what I want is things that 
>aren't in the set of (has certain attribute).
>
>Here's some sample data to illustrate what I'm talking about:
>
>Table A:
>fields: nameid, name
>1, Joe
>2, Mary
>3, Bob
>4, Jim
>
>Table B:
>fields: thing, nameid (used in join)
>10, 1
>10, 2
>11, 1
>11, 3
>
>My main query was this (and I'll add the details this time):
>select name from A left join B on A.nameid = B.nameid
>where B.thing = "10"
>
>This returns:
>Joe, Mary
>
>Now, what I want is to be able to have a query that returns 
>everything in A that DIDN'T come back in that query.  Specifically:
>Bob, Jim
>
>I can't query where B.thing != "10", because that will include the 
>"11,1" entry, which gets me "Joe", which I don't want.
>
>The bad query I listed before (again, with details added) is:
>select name from A left join B on A.nameid = B.nameid
>where B.thing != "10" or B.thing is NULL
>
>This gets me:
>Joe, Bob, Jim
>
>No good, since I don't want Joe there.
>
>The issue, again, is that it's not good enough to say what doesn't 
>match the "thing" in B.  Things may occur again and again in B, both 
>matching "thing" and not matching "thing".  I want to know 
>specifically the opposite of the first query (i.e. the opposite of 
>"what does match the "thing" in B"), which is a bit different.
>
>Thanks much for any help!  I've been going nuts trying to solve this one.

Sir, load the results of the first query into a TEMPORARY table, and 
then run a difference query on A and the temp table.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Join causing Error?

[Bob Hall]

Sir, I don't see anything wrong with the syntax in your query. If I 
haven't over looked something, and if you haven't misspelled 
anything, then you have a data problem. Since your first query 
returns a Cartesian product, you should get something if the WHERE 
clause returns >= 1 row for each table. So either there are no rows 
in q that meet the conditions
q.active = '1' AND q.global = '1'
or there are no rows in c that meet the condition
c.active = '1'
Run the first query with only the first WHERE condition. If you get 
rows, add the second condition. If that works, add the third. The 
point at which the query stops returning rows is the point at which a 
subset of your WHERE conditions  aren't met by the any of the rows in 
one of the tables.

BTW, why are you putting quotes around numbers? I'm assuming that's 
not the problem, since you say you did it in the query that worked, 
but I don't see the point.

Bob Hall

>Can anyone tell me why this:
>   Line 282mysql_select_db("centraldb",$db);
>   Line 283$qorder++;
>   Line 284$result = mysql_query("SELECT q.questid, q.question, 
>q.answer, q.qorder, q.depart, q.catid,
>   q.active, q.global, q.adate, q.author, 
>q.authoremail, q.askemail, c.catid, c.category, c.under,
>   c.corder, c.active FROM central_groupfaqq q, 
>central_groupfaqcat c WHERE q.active = '1' AND
>   q.global = '1' AND c.active = '1' ORDER BY 
>c.under, c.order, q.qorder",$db);
>   Line 285while ($myrow = mysql_fetch_row($result))
>
>Would cause this error:
>   Warning: Supplied argument is not a valid MySQL result resource in 
>faqbody.php3 on line 285
>
>When changing the same SELECT statement to:
>   Line 284$result = mysql_query("SELECT * FROM central_groupfaqq 
>WHERE active = '1' ORDER BY
>qorder",$db);
>
>Works perfectly?
>
>
>Keith Spiller
>a.k.a. Larentium

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] MySQL execution order...

[Bob Hall]

Sir, it would help to see the INSERT statement and the table definition.

Bob Hall

>I sent this message to the "general" list yesterday, and have yet to get an
>answer (except for that Spanish "Snow White" virus attempt from Peru).
>
>I'm trying to download info from a table as a tab-delimted text file. This
>part works fine. (By disabling the UPDATE statement in the code sample
>below, I can get the same data file sent time after time.)
>
>I need to keep the data in the table for future reference, and only want to
>download each new record once, so I added a DateTime field that gets updated
>after downloading. Then searching on that field gives me just the records
>that haven't been downloaded yet.
>
>The weird part is that it seems like the LAST MySQL command (UPDATE) gets
>executed before the FIRST one (SELECT), because the SELECT statement comes
>up with 0 rows.
>
>
>Here's some selected (modified) parts of the PHP code:
>
>
>First, I perform the SQL:
>SELECT * from db_table WHERE (DownloadedDateTime = '-00-00 00:00:00')
>
>Then, it parses and sends the info:
>while ($row = mysql_fetch_row($result)) {
> while (list($key, $val) = each($row)) {
>$val = ereg_replace("[\n\r\t]"," ",$val);
>$val = ereg_replace(" {2,}"," ",$val);
>echo $val . "\t";
> }
> //end of line
> echo "\r";
>}
>
>Then I perform the SQL:
>UPDATE LOW_PRIORITY db_table SET DownloadedDateTime=CURRENT_TIMESTAMP WHERE
>(DownloadedDateTime = '-00-00 00:00:00')
>--
>
>Any ideas why the rows are getting the DownloadedDateTime field updated
>before they get SELECTed?
>
>
>Thanks,
>
>Don Johnson
>[EMAIL PROTECTED]
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] composite keys

[Bob Hall]

>Hi all:
>I am using php4.0.3pl1 with MySQL 3.22.32 on Apache for RH Linux.
>
>I have a table/relation of sports, where amongst other categories, are
>teams, where there can be many 'teamNames' & 'teamInfos' for one sport.
>
>The problem here is that without the use of a composite key:
>'sportName' & 'TeamName', there can be no method of uniquely
>identifiying a row or tuple. A smuch of the info for each sport is
>repeated.
>
>Does MySQL support the use of composite keys? I am using phpMyAdmin
>2.1.0 to adminster my MySQL system, and this only seems to accept a
>single primary key for a single table.
>
>Any advice or other methods I could possibley employ, are most welcome.
>
>Regards:
>Russ

Yes sir, you can declare multi-column primary keys. After the column 
definitions, type
PRIMARY KEY ()
This is covered in the online manual.

If you want to use an auto_increment field as the primary key, you 
can insure uniqueness in the SportName and TeamName fields by 
declaring a UNIQUE INDEX in the same way.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] rewriting this query to remove IN

[Bob Hall]

>Hi Guys,
>
>I understand that as of version 3.24, MySQL will support subselects. But
>untill then (because 3.23 is the latest stable release) I need to convert
>this SQL satement into something that can be used with v3.2? - the version
>just before the latest release.
>
>$search = "SELECT count(search_table.word) as score,
>search_table.qid,page_data.contents
>  FROM search_table,page_data
>  WHERE page_data.pID = search_table.qid AND search_table.word
>  IN($querywords)
>  GROUP BY search_table.qid
>  ORDER BY score DESC";
>
>Does anybody have any ideas, thanks for this guys. Any help is much
>appreciated.
>
>Scott Mebberson

Sir, I can't offer suggestions without knowing what the subquery is.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] the SET datatype

[Bob Hall]

>I have a little MySQL related problem...
>
>The column A.1 is using the SET datatype. I want the possible values of
>A.1 to be any value(s) of column B.1
>I could of cause manually enter the values of B.1 when I create A.1, but
>I want the possible values of A.1
>to change as table B changes. Is there anyway of doing this?
>
>Please help?
>
>[EMAIL PROTECTED]

No sir. The members of the SET have to be entered as constant values 
in the column definition.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

RE: [PHP-DB] foreign key problem

[Bob Hall]

Yes sir, you are correct. Thank you for correcting me. In practice, a 
NULL in a foreign key almost always means an orphan record. In most 
applications the designer will want to use the NOT NULL constraint.

Bob Hall

>A foreign key value can be null, if it suits the data application - or that
>is what I've always been taught.
>
>Here is a text book definition of Referential Integrity which was spoon fed
>to me by the Open University when studying their RDBMS course:
>
>"Referential Integrity - If a relation (table), R2 has a foreign key, F,
>that references the primary key, P, in another relation (table), R1, then
>every R2.F entry must either be a value equal to an R1.P primary key value
>or be null"
>
>Regards,
>Chris
>
>  > -Original Message-
>  > From: Bob Hall [mailto:[EMAIL PROTECTED]]
>  > Sent: 31 January 2001 01:53
>  > To: [EMAIL PROTECTED]
>  > Subject: Re: [PHP-DB] foreign key problem
>  >
>  >
>  > >hello all!
>  > >i have a little problem ,hope someone can help me out
>  > >the problem is :
>  > >let i have two tables T1 and T2 ,now T1 has following
>  > >fields
>  > >cntryid
>  > >cntryname
>  > >cntrycode etc.
>  > >now cntryid is the primary key now i want to make the
>  > >cntryid a foreign key in table T2 .so how can i do it
>  > >plz help me .
>  > >msjamal
>  >
>  > A column is a foreign key because it contains only values found in
>  > the referenced key, and no NULLs. Design your database so that the
>  > foreign key column contains only values from cntryid.
>  >
>  > If you want to know how to declare a referenced key/foreign key
>  > relationship, you will have to specify what RDBMS you are using.
>  >
>  > Bob Hall
>  >
>  > Know thyself? Absurd direction!
>  > Bubbles bear no introspection. -Khushhal Khan Khatak
>  >
>  > --
>  > PHP Database Mailing List (http://www.php.net/)
>  > To unsubscribe, e-mail: [EMAIL PROTECTED]
>  > For additional commands, e-mail: [EMAIL PROTECTED]
>  > To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] foreign key problem

[Bob Hall]

>hello all!
>i have a little problem ,hope someone can help me out
>the problem is :
>let i have two tables T1 and T2 ,now T1 has following
>fields
>cntryid
>cntryname
>cntrycode etc.
>now cntryid is the primary key now i want to make the
>cntryid a foreign key in table T2 .so how can i do it
>plz help me .
>msjamal

A column is a foreign key because it contains only values found in 
the referenced key, and no NULLs. Design your database so that the 
foreign key column contains only values from cntryid.

If you want to know how to declare a referenced key/foreign key 
relationship, you will have to specify what RDBMS you are using.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] Query Access DB by date

[Bob Hall]

>When I try to query an Acess database for a particular date I get ODBC
>errors indicating I'm screwing up the call. Can anyone give me an example of
>a select on a date field. Quotes make it think it is a string. No quotes
>make it think it is a number. The pound sign just says it doesn't know what
>I'm asking for. I'm confused. Thanks.

The pound sign is correct. If your query doesn't work with the pound 
sign, then the problem is elsewhere. Post an example of your code.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]