Re: [PHP] database hell
On 15/07/2012 17:07, Nick Edwards wrote: On 7/12/12, Ashley Sheridan a...@ashleysheridan.co.uk wrote: ma...@behnke.biz [5] ma...@behnke.biz [6] wrote: Nick Edwards nick.z.edwa...@gmail.com [2] hat am 12. Juli 2012 um 14:00 geschrieben: On 7/12/12, Gibbs li...@danielgibbs.net [1] wrote: mysql_query(DELETE from userprefs where clientr='$User', $connmy); Sidenote: And don't forget to validate user input and make use of mysql escape and prepared statements ;) -- PHP General Mailing List (http://www.php.net/ [3]) To unsubscribe, visit: http://www.php.net/unsub.php [4] Another way if the access credentials are the same would be to specify the full 'path' to the table in the query: DELETE FROM database.table WHERE clause Umm I wouldn't be doing that if using mysql replication, I only now (2 days later) discovered that broke it! but your suggestion was the only one that allowed it to work without crashing out for unauthed access to (wrong) database when using db1 and db2 (worked until it needed to return to db1, strill tried to use db2 method, hrmm at least perl knows to return to use the original, not php though) thanks to all suggestions, looks like we just need to close db1 con db2, close db2 and recon to db1 *sigh* Google for mysql replicate-ignore-table this will stop any replication errors because of unknown tables, well, if configured correctly :) -- If you are not the intended recipient, please notify the sender and delete all relevance of this message including any attachments immediately. Please do not send Microsoft proprietary formatted documents, instead use ODF or PDF. Links: -- [1] mailto:li...@danielgibbs.net [2] mailto:nick.z.edwa...@gmail.com [3] http://www.php.net/ [4] http://www.php.net/unsub.php [5] mailto:ma...@behnke.biz [6] mailto:ma...@behnke.biz
Re: [PHP] database hell
On 7/12/12, Ashley Sheridan a...@ashleysheridan.co.uk wrote: ma...@behnke.biz ma...@behnke.biz wrote: Nick Edwards nick.z.edwa...@gmail.com hat am 12. Juli 2012 um 14:00 geschrieben: On 7/12/12, Gibbs li...@danielgibbs.net wrote: mysql_query(DELETE from userprefs where clientr='$User', $connmy); Sidenote: And don't forget to validate user input and make use of mysql escape and prepared statements ;) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Another way if the access credentials are the same would be to specify the full 'path' to the table in the query: DELETE FROM database.table WHERE clause Umm I wouldn't be doing that if using mysql replication, I only now (2 days later) discovered that broke it! but your suggestion was the only one that allowed it to work without crashing out for unauthed access to (wrong) database when using db1 and db2 (worked until it needed to return to db1, strill tried to use db2 method, hrmm at least perl knows to return to use the original, not php though) thanks to all suggestions, looks like we just need to close db1 con db2, close db2 and recon to db1 *sigh* -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] database hell
Hi We have a program that manages users, throughout all database calls created as: $connect = mysql_connect($db_host--other variables); mysql_query(Delete from clients where id=$User); All this works good, but, we need, in the delete function to delete from another database $connmy=mysql_connect(host,user,pass); mysql_select_db(vsq,$connmy); mysql_query(DELETE from userprefs where clientr='$User'); $mysql_close($connmy); this fails, unless we use a mysql_close prior to it, and then reconnect to original database after we run this delete, how can we get around this without closing and reopening? We have a perl script doing similar for manual runs, and it works well knowing that $connmy is not $connect, I'm sure there is a simple way to tell php but I'm darned if I can see it. Thanks Niki -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
On 12/07/12 12:29, Nick Edwards wrote: Hi We have a program that manages users, throughout all database calls created as: $connect = mysql_connect($db_host--other variables); mysql_query(Delete from clients where id=$User); All this works good, but, we need, in the delete function to delete from another database $connmy=mysql_connect(host,user,pass); mysql_select_db(vsq,$connmy); mysql_query(DELETE from userprefs where clientr='$User'); $mysql_close($connmy); this fails, unless we use a mysql_close prior to it, and then reconnect to original database after we run this delete, how can we get around this without closing and reopening? We have a perl script doing similar for manual runs, and it works well knowing that $connmy is not $connect, I'm sure there is a simple way to tell php but I'm darned if I can see it. Thanks Niki You need to make a new link. So you would add TRUE to the end of the second connection. $connmy=mysql_connect(host,user,pass, TRUE); http://php.net/manual/en/function.mysql-connect.php Gibbs -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
On 7/12/12, Gibbs li...@danielgibbs.net wrote: On 12/07/12 12:29, Nick Edwards wrote: Hi We have a program that manages users, throughout all database calls created as: $connect = mysql_connect($db_host--other variables); mysql_query(Delete from clients where id=$User); All this works good, but, we need, in the delete function to delete from another database $connmy=mysql_connect(host,user,pass); mysql_select_db(vsq,$connmy); mysql_query(DELETE from userprefs where clientr='$User'); $mysql_close($connmy); this fails, unless we use a mysql_close prior to it, and then reconnect to original database after we run this delete, how can we get around this without closing and reopening? We have a perl script doing similar for manual runs, and it works well knowing that $connmy is not $connect, I'm sure there is a simple way to tell php but I'm darned if I can see it. Thanks Niki You need to make a new link. So you would add TRUE to the end of the second connection. $connmy=mysql_connect(host,user,pass, TRUE); http://php.net/manual/en/function.mysql-connect.php Thanks, will give that a shot -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
On 12 Jul 2012, at 12:29, Nick Edwards wrote: We have a program that manages users, throughout all database calls created as: $connect = mysql_connect($db_host--other variables); mysql_query(Delete from clients where id=$User); All this works good, but, we need, in the delete function to delete from another database $connmy=mysql_connect(host,user,pass); mysql_select_db(vsq,$connmy); mysql_query(DELETE from userprefs where clientr='$User'); $mysql_close($connmy); this fails, unless we use a mysql_close prior to it, and then reconnect to original database after we run this delete, how can we get around this without closing and reopening? We have a perl script doing similar for manual runs, and it works well knowing that $connmy is not $connect, I'm sure there is a simple way to tell php but I'm darned if I can see it. The mysql_query method takes a second parameter specifying the MySQL connection to which to send the query. If you're dealing with multiple hosts you will want to add that to every single mysql_* function call that supports it, otherwise you could end up running queries on the wrong database. You may also want to note that using the mysql_* functions is now discouraged in favour of MySQLi or PDO: http://php.net/mysqlinfo.api.choosing -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/
RE: [PHP] database hell
-Original Message-
From: Nick Edwards [mailto:nick.z.edwa...@gmail.com]
Sent: 12 July 2012 12:30
To: php-general@lists.php.net
Subject: [PHP] database hell
Hi
We have a program that manages users, throughout all database calls
created as:
$connect = mysql_connect($db_host--other variables);
mysql_query(Delete from clients where id=$User);
All this works good, but, we need, in the delete function to delete from
another database
$connmy=mysql_connect(host,user,pass);
mysql_select_db(vsq,$connmy);
mysql_query(DELETE from userprefs where
clientr='$User');
$mysql_close($connmy); this fails, unless we use a mysql_close prior to it,
and then reconnect to original database after we run this delete, how can we
get around this without closing and reopening?
We have a perl script doing similar for manual runs, and it works well
knowing that $connmy is not $connect, I'm sure there is a simple way to tell
php but I'm darned if I can see it.
Thanks
Niki
--
PHP General Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php
Just create a new resource/connection to MySQL and pass the identifier into
mysql_query().
You'll also want to use mysql_real_escape_string() by the looks of it to
attempt to stop SQL injection.
Something like this will do it:
$db1 = mysql_connect($host,$user,$pass);
$db2 = mysql_connect($host,$user,$pass);
mysql_select_db('db1',$db1);
mysql_select_db('db2',$db2);
// do your queries with $DB1
$result = mysql_query(delete from userprefs where
clientr=.mysql_real_escape_string($user,$db1)., $db1);
// do your queries again with $DB1
mysql_close($db1);//close db1
mysql_close($db2);//close db2
Cheers
Adam.
=
This email is intended solely for the recipient and is confidential and not for
third party unauthorised distribution. If an addressing or transmission error
has misdirected this email, please notify the author by replying to this email
or notifying the system manager (online.secur...@hl.co.uk). If you are not the
intended recipient you must not disclose, distribute, copy, print or rely on
this email.
Any opinions expressed in this document are those of the author and do not
necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are
not authorised to enter into any contract through email and therefore nothing
contained herein should be construed as such. Hargreaves Lansdown makes no
warranty as to the accuracy or completeness of any information contained within
this email. In particular, Hargreaves Lansdown does not accept responsibility
for any changes made to this email after it was sent.
Hargreaves Lansdown Asset Management Limited (Company Registration No 1896481),
Hargreaves Lansdown Fund Managers Limited (No 2707155), Hargreaves Lansdown
Pensions Direct Limited (No 3509545) and Hargreaves Lansdown Stockbrokers
Limited (No 1822701) are authorised and regulated by the Financial Services
Authority and registered in England and Wales. The registered office for all
companies is One College Square South, Anchor Road, Bristol, BS1 5HL.
Telephone: 0117 988 9880
__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__
Re: [PHP] database hell
On 12/07/12 12:38, Nick Edwards wrote: On 7/12/12, Gibbs li...@danielgibbs.net wrote: On 12/07/12 12:29, Nick Edwards wrote: Hi We have a program that manages users, throughout all database calls created as: $connect = mysql_connect($db_host--other variables); mysql_query(Delete from clients where id=$User); All this works good, but, we need, in the delete function to delete from another database $connmy=mysql_connect(host,user,pass); mysql_select_db(vsq,$connmy); mysql_query(DELETE from userprefs where clientr='$User'); $mysql_close($connmy); this fails, unless we use a mysql_close prior to it, and then reconnect to original database after we run this delete, how can we get around this without closing and reopening? We have a perl script doing similar for manual runs, and it works well knowing that $connmy is not $connect, I'm sure there is a simple way to tell php but I'm darned if I can see it. Thanks Niki You need to make a new link. So you would add TRUE to the end of the second connection. $connmy=mysql_connect(host,user,pass, TRUE); http://php.net/manual/en/function.mysql-connect.php Thanks, will give that a shot I forgot to add your queries will need the new link too. So mysql_query(DELETE from userprefs where clientr='$User', $connmy); Gibbs -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
On 7/12/12, Adam Nicholls adam.nicho...@hl.co.uk wrote:
-Original Message-
From: Nick Edwards [mailto:nick.z.edwa...@gmail.com]
Sent: 12 July 2012 12:30
To: php-general@lists.php.net
Subject: [PHP] database hell
Hi
We have a program that manages users, throughout all database calls
created as:
$connect = mysql_connect($db_host--other variables);
mysql_query(Delete from clients where id=$User);
All this works good, but, we need, in the delete function to delete from
another database
$connmy=mysql_connect(host,user,pass);
mysql_select_db(vsq,$connmy);
mysql_query(DELETE from userprefs where
clientr='$User');
$mysql_close($connmy); this fails, unless we use a mysql_close prior to
it,
and then reconnect to original database after we run this delete, how can
we
get around this without closing and reopening?
We have a perl script doing similar for manual runs, and it works well
knowing that $connmy is not $connect, I'm sure there is a simple way to
tell
php but I'm darned if I can see it.
Thanks
Niki
--
PHP General Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php
Just create a new resource/connection to MySQL and pass the identifier into
mysql_query().
You'll also want to use mysql_real_escape_string() by the looks of it to
attempt to stop SQL injection.
Something like this will do it:
$db1 = mysql_connect($host,$user,$pass);
$db2 = mysql_connect($host,$user,$pass);
mysql_select_db('db1',$db1);
mysql_select_db('db2',$db2);
// do your queries with $DB1
$result = mysql_query(delete from userprefs where
clientr=.mysql_real_escape_string($user,$db1)., $db1);
// do your queries again with $DB1
mysql_close($db1);//close db1
mysql_close($db2);//close db2
We can not immediately close db2? if we do it seems to close all connections?
Thanks
Cheers
Adam.
=
This email is intended solely for the recipient and is confidential and not
for third party unauthorised distribution. If an addressing or transmission
error has misdirected this email, please notify the author by replying to
this email or notifying the system manager (online.secur...@hl.co.uk). If
you are not the intended recipient you must not disclose, distribute, copy,
print or rely on this email.
Any opinions expressed in this document are those of the author and do not
necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff
are not authorised to enter into any contract through email and therefore
nothing contained herein should be construed as such. Hargreaves Lansdown
makes no warranty as to the accuracy or completeness of any information
contained within this email. In particular, Hargreaves Lansdown does not
accept responsibility for any changes made to this email after it was sent.
Hargreaves Lansdown Asset Management Limited (Company Registration No
1896481), Hargreaves Lansdown Fund Managers Limited (No 2707155), Hargreaves
Lansdown Pensions Direct Limited (No 3509545) and Hargreaves Lansdown
Stockbrokers Limited (No 1822701) are authorised and regulated by the
Financial Services Authority and registered in England and Wales. The
registered office for all companies is One College Square South, Anchor
Road, Bristol, BS1 5HL. Telephone: 0117 988 9880
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
On 7/12/12, Gibbs li...@danielgibbs.net wrote: $connmy=mysql_connect(host,user,pass, TRUE); http://php.net/manual/en/function.mysql-connect.php Thanks, will give that a shot I forgot to add your queries will need the new link too. So mysql_query(DELETE from userprefs where clientr='$User', $connmy); Got that, ta :) Gibbs -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
Nick Edwards nick.z.edwa...@gmail.com hat am 12. Juli 2012 um 14:00 geschrieben: On 7/12/12, Gibbs li...@danielgibbs.net wrote: mysql_query(DELETE from userprefs where clientr='$User', $connmy); Sidenote: And don't forget to validate user input and make use of mysql escape and prepared statements ;) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database hell
ma...@behnke.biz ma...@behnke.biz wrote: Nick Edwards nick.z.edwa...@gmail.com hat am 12. Juli 2012 um 14:00 geschrieben: On 7/12/12, Gibbs li...@danielgibbs.net wrote: mysql_query(DELETE from userprefs where clientr='$User', $connmy); Sidenote: And don't forget to validate user input and make use of mysql escape and prepared statements ;) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Another way if the access credentials are the same would be to specify the full 'path' to the table in the query: DELETE FROM database.table WHERE clause Thanks, Ash http://ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP Database Problems -- Code Snippets
On Thu, May 3, 2012 at 4:20 PM, Ethan Rosenberg eth...@earthlink.net wrote:
At 06:47 PM 5/2/2012, Matijn Woudt wrote:
On Wed, May 2, 2012 at 11:43 PM, Ethan Rosenberg eth...@earthlink.net
wrote: Dear list - Sorry for the attachment. Â Here are code snippets
--- Ethan, I don't want to sound rude, but it appears to me you don't have
any understanding of what you're doing. It might help if you understand what
the code is doing... Let me explain. GET THE DATA FROM INTAKE3: Â Â
function handle_data() Â Â { Â Â Â global $cxn; Â Â Â $query =
select * from Intake3 where  1;   Â
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' ) $_Request does not
exists, you're looking for $_REQUEST. And why are you mixing $_REQUEST and
$_POST here? Â Â Â { Â Â Â Â Â Â if ($_REQUEST['Sex'] === 0)
      {        $sex = 'Male';      Â
}       else       {        $sex =
'Female'; Â Â Â Â Â Â } Â Â Â } Â Â } What is the point of
the handle_data function above? It doesn't do anything. Â Â
$allowed_fields = array    (  'Site' =$_POST['Site'], 'MedRec' =
$_POST['MedRec'], 'Fname' = $_POST['Fname'], 'Lname' = $_POST['Lname'] ,
      'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex']  ,
'Height' = $_POST['Height'] Â ); Â Â if(empty($allowed_fields)) Â
 {      echo ouch;   }   $query = select * from
Intake3  where  1 ;   foreach ( $allowed_fields as $key = $val )
  {    if ( (($val != '')) )   {    $query .=
AND ($key  = '$val') ;   }    $result1 = mysqli_query($cxn,
$query); Â Â } First, this will allow SQL injections, because you insert
the values directly from the browser. Second, you should move the last line
($result1=...), outside of the foreach loop, now you're executing the query
multiple times. Third, you should check if $result1 === FALSE, in case the
query fails   $num = mysqli_num_rows($result1);   if(($num =
mysqli_num_rows($result1)) == 0) Doing the same thing twice? Â Â { ?
  br /br /centerbp style=color: red; font-size:14pt; No
Records Retrieved #1/center/b/style/p ?php   exit(); Â
 } DISPLAY THE INPUT3 DATA: THIS SEEMS TO BE THE ROUTINE THAT
IS FAILINGÂ Â centerbSearch Results/b/centerbr / Â
 centertable border=4 cellpadding=5 cellspacing=55  rules=all
 frame=box   tr class=\heading\   thSite/th  Â
thMedical Record/th   thFirst Name/th   thLast Name/th
  thPhone/td   thHeight/td   thSex/td  Â
thHistory/td   /tr ?php    while ($row1 =
mysqli_fetch_array($result1, MYSQLI_BOTH)) Â Â Â { Â Â Â Â Â Â
print_r($_POST); Doesn't really make sense to print $_POST here.. Â Â Â
    global $MDRcheck;        $n1++;     Â
  echo br /n1 br /;echo $n1;       {     Â
  if (($n1 2) ($MDRcheck == $row1[1]))        {
          echo 2==  ;          Â
echo $MDRcheck; Â Â Â Â Â Â Â Â Â Â echo td $row1[0] /td\n;
          echo td $row1[1] /td\n;     Â
     echo td $row1[2] /td\n;          Â
echo td $row1[3] /td\n; Â Â Â Â Â Â Â Â Â Â echo td
$row1[4] /td\n; Â Â Â Â Â Â Â Â Â Â echo td $row1[5]
/td\n; Â Â Â Â Â Â Â Â Â Â echo td $row1[6] /td\n; Â
         echo td $row1[7] /td\n;      Â
    echo /tr\n;        }       Â
elseif (($n1 2) ($MDRcheck != $row1[1])) Â Â Â Â Â Â Â { Â
         echo 2!=  ;          Â
echo $MDRcheck;Â Â Â Â Â Â Â Â Â Â continue; continue
doesn't do anything here. Â Â Â Â Â Â Â } Â Â Â Â Â Â Â
elseif ($n1 == 2) Â Â Â Â Â Â Â { Â Â Â Â Â Â Â Â Â Â
define( MDR , Â $row1[1]); Â Â Â Â Â Â Â Â Â Â echo br /row1
br;echo $row1[1]; Â Â Â Â Â Â Â Â Â Â echo tr\n; Â Â
        $_GLOBALS['mdr']= $row1[1];        Â
  $_POST['MedRec'] = $row1[1]; You're not supposed to set variables in
$_POST... Â Â Â Â Â Â Â Â Â Â $MDRold = $_GLOBALS['mdr']; It
appears you want the old value of mdr, if so, then you should do this before
you set it again 2 lines above.. Â Â Â Â Â Â Â Â Â Â echo td
$row1[0] /td\n; Â Â Â Â Â Â Â Â Â Â echo td $row1[1]
/td\n; Â Â Â Â Â Â Â Â Â Â echo td $row1[2] /td\n; Â
         echo td $row1[3] /td\n;      Â
    echo td $row1[4] /td\n;          Â
echo td $row1[5] /td\n; Â Â Â Â Â Â Â Â Â Â echo td
$row1[6] /td\n; Â Â Â Â Â Â Â Â Â Â echo td $row1[7]
/td\n; Â Â Â Â Â Â Â Â Â
[PHP] PHP Database Problems -- Code Snippets - Any more Ideas?
I am sending this again to see if more ideas for solution of this
problem are available.
Ethan
===
Dear list -
Sorry for the attachment. Here are code snippets ---
GET THE DATA FROM INTAKE3:
function handle_data()
{
global $cxn;
$query = select * from Intake3 where 1;
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' )
{
if ($_REQUEST['Sex'] === 0)
{
$sex = 'Male';
}
else
{
$sex = 'Female';
}
}
}
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'],
'Fname' = $_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] ,
'Height' = $_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
}
$result1 = mysqli_query($cxn, $query);
}
$num = mysqli_num_rows($result1);
if(($num = mysqli_num_rows($result1)) == 0)
{
?
br /br /centerbp style=color: red;
font-size:14pt; No Records Retrieved #1/center/b/style/p
?php
exit();
}
DISPLAY THE INPUT3 DATA:
THIS SEEMS TO BE THE ROUTINE THAT IS FAILING
centerbSearch Results/b/centerbr /
centertable border=4 cellpadding=5
cellspacing=55 rules=all frame=box
tr class=\heading\
thSite/th
thMedical Record/th
thFirst Name/th
thLast Name/th
thPhone/td
thHeight/td
thSex/td
thHistory/td
/tr
?php
while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH))
{
print_r($_POST);
global $MDRcheck;
$n1++;
echo br /n1 br /;echo $n1;
{
if (($n1 2) ($MDRcheck == $row1[1]))
{
echo 2== ;
echo $MDRcheck;
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
elseif (($n1 2) ($MDRcheck != $row1[1]))
{
echo 2!= ;
echo $MDRcheck;
continue;
}
elseif ($n1 == 2)
{
define( MDR , $row1[1]);
echo br /row1 br;echo $row1[1];
echo tr\n;
$_GLOBALS['mdr']= $row1[1];
$_POST['MedRec'] = $row1[1];
$MDRold = $_GLOBALS['mdr'];
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
}
}
?
SELECT AND DISPLAY DATA FROM VISIT3 DATABASE
?php
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND
(MedRec = $_GLOBALS[mdr]);
$result2 = mysqli_query($cxn, $query2);
$num = mysqli_num_rows($result2);
global $finished;
$finished = 0;
while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
global $finished;
echo tr\n;
echo td $row2[0] /td\n;
echo td $row2[1] /td\n;
echo td $row2[2] /td\n;
echo td $row2[3] /td\n;
echo td $row2[4] /td\n;
echo td $row2[5] /td\n;
echo td $row2[6] /td\n;
echo /tr\n;
}
echo /table;
ENTER MORE DATA:
function More_Data()
{
$decision = 5;
?
Do you Wish to Enter More Data?
form method=post action=
centerinput type=radio name=decision value=1 /Yes
input type=radio name=decision value=0 /No/centerbr /
centerinput type=submit value=Enter more Data //center
input type=hidden name=next_step value=step10 /
/form
?php
} //end function More_Data
switch ( @$_POST[next_step] )
{
case step10:
{
if (!isset($_POST['decision']))
{
$_POST['decision'] = 5;
}
if ($_POST['decision'] == 0)
{
exit();
}
if ($_POST['decision'] == 1)
{
;
echo form method=\post\ action=\\;
echo input type=\hidden\ name=\next_step\
value=\step4\ /;
echo enterbr /;
echo Medical Record: nbspinput
Re: [PHP] PHP Database Problems -- Code Snippets - Any more Ideas?
I don't think posting the same voluminous code is going to generate any better responses. The suggestion to start over and make your insert/retrieve queries fool proof before starting to write some logic into your code was a very good one. Why don't you work on that so that any requests for help can focus on just that instead of the 100+ lines of code you are posting? Reading some documentation on sql and some html/php relationships would be VERY beneficial to your long-term success as a programmer as well. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP Database Problems -- Code Snippets - Any more Ideas?
Nor will posting to multiple lists.(Sorry to the rest of you - didn't realize it until now.) Jim Giner jim.gi...@albanyhandball.com wrote in message news:c6.f8.38082.efae3...@pb1.pair.com... I don't think posting the same voluminous code is going to generate any better responses. The suggestion to start over and make your insert/retrieve queries fool proof before starting to write some logic into your code was a very good one. Why don't you work on that so that any requests for help can focus on just that instead of the 100+ lines of code you are posting? Reading some documentation on sql and some html/php relationships would be VERY beneficial to your long-term success as a programmer as well. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP Database Problems -- Code Snippets - Any more Ideas?
Ethan, before you get frustrated or feel abandoned, let me *sincerely* try to help: Here is honestly what I am utterly convinced you need to do to get any where in the medium/long run: Break everything down into very small steps. Making web apps is just building a whole lot of layers/collections of very small simple things. Nothing is out of your grasp.. it only appears mysterious or complicated because you are not familiar with the terms/code structures and have not spent enough time with the little things to recognize them like english speakers do when reading written english. So what you do to get out of the woods (and it works every time), regardless of the problem, is just focus on one small thing at a time. START OVER. Throw out all the code, especially everything you borrowed from other sources. Write everything yourself, from scratch. Just print hello to a web page. Then add in the ability to do ONE more thing that you need... like reading from a db, or writing to a db... and then processing/manipulating things, as you need. Test everything every time you add even ONE little feature. Then as soon as something breaks, you know instantly where the issue lays.. and focus on solving why that one little thing is broken. If re-reading the docs about whatever code structures you used at that point (of your newly-added broken feature) does not clear it up for you, then post just that ONE little issue to this list.. asking why that one thing is behaving that way. Like this (using this tedious but effective method) , you will get your code into shape so it works, you will not alienate yourself from the help you need (by posting volumes of broken code with no evidence that you are actually trying to learn), and best of all - you will, step by step, come to master all this stuff! Everyone loves to help answer/clear up one little thing, but no one has time to digest a whole broken page/app and tell you where all the issues are. Even if they did have the time and inclination, they would lose it after the very first time they saw you take what they gave you and come back 3 weeks later with evidence that you never learned anything from the last episode. Believe me you will always have people climbing over each other to help you, if you can just break down your problems into such small portions that you will be able to realize you have the smarts to answer them yourself. ;-) There is a very lively, effective and popular coders community (and Q/A tool set) here: http://stackoverflow.com/ ..where you get almost instant help to any coding question.. because there are so many people who really care to give quality help, because they get recognized for their contributions. But if you try to use the tools at stackoverflow.com then you will find there, in that very professional atmosphere, that (to get anywhere) you HAVE to ask questions that are distilled down to something very specific and answerable in a specific/factual kind of way, as opposed to question that bring up more fuzzy-boundaried topics, like questions of preference or style, or questions that show an utter lack of homework/effort on the part of the asker which require more than a couple specific facts to answer. You can train here or on stackoverflow.com, but anywhere you go, you will find the same situation, that you have to use baby steps (as necessary) - for your own learning, and to get any decent help. -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] RE: [PHP-DB] [PHP] PHP Database Problems -- Code Snippets - Any more Ideas?
Ethan,
You have been given advise and break down on your code.
Have you taken the advise given?
Which part of the code isn't working? Not which chunk, but break it down and
show which part
BR,
Gav
-Original Message-
From: Ethan Rosenberg [mailto:eth...@earthlink.net]
Sent: 04 May 2012 15:10
To: php-db-lists.php.net; php-general@lists.php.net
Subject: [PHP-DB] [PHP] PHP Database Problems -- Code Snippets - Any more
Ideas?
I am sending this again to see if more ideas for solution of this problem
are available.
Ethan
===
Dear list -
Sorry for the attachment. Here are code snippets ---
GET THE DATA FROM INTAKE3:
function handle_data()
{
global $cxn;
$query = select * from Intake3 where 1;
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' )
{
if ($_REQUEST['Sex'] === 0)
{
$sex = 'Male';
}
else
{
$sex = 'Female';
}
}
}
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' =
$_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] ,
'Height' = $_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
}
$result1 = mysqli_query($cxn, $query);
}
$num = mysqli_num_rows($result1);
if(($num = mysqli_num_rows($result1)) == 0)
{
?
br /br /centerbp style=color: red; font-size:14pt; No
Records Retrieved #1/center/b/style/p ?php
exit();
}
DISPLAY THE INPUT3 DATA:
THIS SEEMS TO BE THE ROUTINE THAT IS FAILING
centerbSearch Results/b/centerbr /
centertable border=4 cellpadding=5
cellspacing=55 rules=all frame=box
tr class=\heading\
thSite/th
thMedical Record/th
thFirst Name/th
thLast Name/th
thPhone/td
thHeight/td
thSex/td
thHistory/td
/tr
?php
while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH))
{
print_r($_POST);
global $MDRcheck;
$n1++;
echo br /n1 br /;echo $n1;
{
if (($n1 2) ($MDRcheck == $row1[1]))
{
echo 2== ;
echo $MDRcheck;
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
elseif (($n1 2) ($MDRcheck != $row1[1]))
{
echo 2!= ;
echo $MDRcheck;
continue;
}
elseif ($n1 == 2)
{
define( MDR , $row1[1]);
echo br /row1 br;echo $row1[1];
echo tr\n;
$_GLOBALS['mdr']= $row1[1];
$_POST['MedRec'] = $row1[1];
$MDRold = $_GLOBALS['mdr'];
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
}
}
?
SELECT AND DISPLAY DATA FROM VISIT3 DATABASE
?php
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec
= $_GLOBALS[mdr]);
$result2 = mysqli_query($cxn, $query2);
$num = mysqli_num_rows($result2);
global $finished;
$finished = 0;
while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
global $finished;
echo tr\n;
echo td $row2[0] /td\n;
echo td $row2[1] /td\n;
echo td $row2[2] /td\n;
echo td $row2[3] /td\n;
echo td $row2[4] /td\n;
echo td $row2[5] /td\n;
echo td $row2[6] /td\n;
echo /tr\n;
}
echo /table;
ENTER MORE DATA:
function More_Data()
{
$decision = 5;
?
Do you Wish to Enter More Data?
form method=post action=
centerinput type=radio name=decision value=1 /Yes
input type=radio name=decision value=0 /No/centerbr /
centerinput type=submit value=Enter more Data //center
input type=hidden name=next_step value=step10 /
/form
?php
} //end
Re: [PHP] PHP Database Problems -- Code Snippets - Any more Ideas?
Am 04.05.2012 16:09, schrieb Ethan Rosenberg:
function handle_data()
{
global $cxn;
What does this function? It neither takes any parameters nor returns any
value. And it does not write back anything to its global $cxn. So it is
quite useless and can be deleted.
$query = select * from Intake3 where 1;
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' )
there is no variable $_Request, it is $_REQUEST.
Why do you test on $_REQUEST and compare it with trimmed $_POST?
{
if ($_REQUEST['Sex'] === 0)
{
$sex = 'Male';
}
else
{
$sex = 'Female';
}
Why do you set a variable that is never used?
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' =
$_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] , 'Height' =
$_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
Why the hell do you put unverified data into an sql query?
DISPLAY THE INPUT3 DATA:
THIS SEEMS TO BE THE ROUTINE THAT IS FAILING
What fails?
I do not have access to your database, so I can not run your code to see
what fails.
?php
while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH))
{
print_r($_POST);
global $MDRcheck;
$n1++;
echo br /n1 br /;echo $n1;
{
if (($n1 2) ($MDRcheck == $row1[1]))
What is $MDRcheck and what does this comparision mean?
SELECT AND DISPLAY DATA FROM VISIT3 DATABASE
?php
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec =
$_GLOBALS[mdr]);
Quotes around mdr missing
$result2 = mysqli_query($cxn, $query2);
$num = mysqli_num_rows($result2);
global $finished;
$finished = 0;
while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
global $finished;
No need to global that twice.
And why ndo you use global and $_GLOBALS? STick to one or better skip it
anyways. Globals are not to be used!
switch ( @$_POST[next_step] )
Remove all @ from your code or you won't see any errors on this.
Do proper checking and do NOT suppress errors or warnings.
echo form method=\post\ action=\\;
echo input type=\hidden\ name=\next_step\ value=\step4\ /;
echo enterbr /;
echo Medical Record: nbspinput type=\text\ name=\MedRec\ value=\
$_GLOBALS[mdr]\ /;
Quotes.
$Weight = $_POST['Weight'];
$Notes = $_POST['Notes'];
$sql2 = INSERT INTO Visit3(Indx, Site, MedRec, Notes, Weight, BMI,
Date) VALUES(null, '$Site', '$MDRold', '$Notes',
Do NOT NEVER put data that is user input unchecked into a query.
?
?
Double closing tag?
echo td $_GLOBALS[mdr] /td\n;
Quotes.
$flag = 1;
What's this?
You really really should seperate your code from HTML.
Please truncate your apache and php error log.
Add
error_reporting(E_ALL);
ini_set('display_errors', 'On');
at the top of every php file right after ?php onto a new line.
Remove all @ from your lines and execute your script another time and
see what errors are appear into your browser and your logfiles. Post
them and the codelines for these errors on the list.
--
Marco Behnke
Dipl. Informatiker (FH), SAE Audio Engineer
Zend Certified Engineer PHP 5.3
Tel.: 0174 / 9722336
e-Mail: ma...@behnke.biz
Softwaretechnik Behnke
Heinrich-Heine-Str. 7D
21218 Seevetal
http://www.behnke.biz
smime.p7s
Description: S/MIME Kryptografische Unterschrift
Re: [PHP] PHP Database Problems -- Code Snippets
At 06:47 PM 5/2/2012, Matijn Woudt wrote:
On Wed, May 2, 2012 at 11:43 PM, Ethan Rosenberg
eth...@earthlink.net wrote: Dear list -
Sorry for the attachment. Â Here are code
snippets --- Ethan, I don't want to sound rude,
but it appears to me you don't have any
understanding of what you're doing. It might
help if you understand what the code is doing...
Let me explain. GET THE DATA FROM
INTAKE3: Â Â function handle_data() Â Â
{ Â Â Â global $cxn; Â Â Â $query =
select * from Intake3 where  1;
   if(isset($_Request['Sex'])
trim($_POST['Sex']) != '' ) $_Request does not
exists, you're looking for $_REQUEST. And why
are you mixing $_REQUEST and $_POST here?
   {       if
($_REQUEST['Sex'] === 0) Â Â Â Â Â Â
{ Â Â Â Â Â Â Â $sex = 'Male';
      }       else
      {        $sex =
'Female'; Â Â Â Â Â Â } Â Â Â }
  } What is the point of the handle_data
function above? It doesn't do anything. Â Â
$allowed_fields = array    (  'Site'
=$_POST['Site'], 'MedRec' = $_POST['MedRec'],
'Fname' = $_POST['Fname'], 'Lname' =
$_POST['Lname'] , Â Â Â Â Â Â 'Phone' =
$_POST['Phone'] , 'Sex' = $_POST['Sex'] Â ,
'Height' = $_POST['Height'] Â ); Â Â
if(empty($allowed_fields)) Â Â {
     echo ouch;   }  Â
$query = select * from Intake3  where  1
; Â Â foreach ( $allowed_fields as $key =
$val ) Â Â { Â Â Â if ( (($val != ''))
) Â Â { Â Â Â $query .= AND ($key Â
= '$val') ; Â Â } Â Â Â $result1 =
mysqli_query($cxn, $query); Â Â } First, this
will allow SQL injections, because you insert
the values directly from the browser. Second,
you should move the last line ($result1=...),
outside of the foreach loop, now you're
executing the query multiple times. Third, you
should check if $result1 === FALSE, in case the
query fails   $num =
mysqli_num_rows($result1); Â Â if(($num =
mysqli_num_rows($result1)) == 0) Doing the same
thing twice? Â Â { ? Â Â br /br
/centerbp style=color: red;
font-size:14pt; No Records Retrieved
#1/center/b/style/p ?php  Â
exit(); Â Â } DISPLAY THE INPUT3
DATA: THIS SEEMS TO BE THE ROUTINE THAT
IS FAILINGÂ Â centerbSearch
Results/b/centerbr / Â Â
centertable border=4 cellpadding=5
cellspacing=55 Â rules=all Â
frame=box   tr class=\heading\
  thSite/th   thMedical
Record/th   thFirst Name/th  Â
thLast Name/th   thPhone/td  Â
thHeight/td   thSex/td  Â
thHistory/td   /tr ?php
   while ($row1 =
mysqli_fetch_array($result1, MYSQLI_BOTH))
   {       print_r($_POST);
Doesn't really make sense to print $_POST
here.. Â Â Â Â Â Â Â global
$MDRcheck; Â Â Â Â Â Â Â $n1++;
       echo br /n1 br /;echo
$n1; Â Â Â Â Â Â {
       if (($n1 2) ($MDRcheck
== $row1[1])) Â Â Â Â Â Â Â {
          echo 2==  ;
          echo $MDRcheck;
          echo td $row1[0]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[1] /td\n;
          echo td $row1[2]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[3] /td\n;
          echo td $row1[4]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[5] /td\n;
          echo td $row1[6]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[7] /td\n;
          echo /tr\n;
       }
       elseif (($n1 2)
($MDRcheck != $row1[1]))
       {
          echo 2!=  ;
          echo
$MDRcheck;Â Â Â Â Â Â Â Â Â Â
continue; continue doesn't do anything here.
       }
       elseif ($n1 == 2)
       {
          define( MDR , Â
$row1[1]); Â Â Â Â Â Â Â Â Â Â echo
br /row1 br;echo $row1[1];
          echo tr\n;
          $_GLOBALS['mdr']=
$row1[1]; Â Â Â Â Â Â Â Â Â Â
$_POST['MedRec'] = $row1[1]; You're not supposed
to set variables in $_POST...
          $MDRold =
$_GLOBALS['mdr']; It appears you want the old
value of mdr, if so, then you should do this
before you set it again 2 lines above..
          echo td $row1[0]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[1] /td\n;
          echo td $row1[2]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[3] /td\n;
          echo td $row1[4]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[5] /td\n;
          echo td $row1[6]
/td\n; Â Â Â Â Â Â Â Â Â Â echo
td $row1[7] /td\n;
          echo /tr\n;
  Â
[PHP] PHP Database Problems
have a database mysql describe Intake3; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Site | varchar(6) | NO | PRI | | | | MedRec | int(6) | NO | PRI | NULL| | | Fname | varchar(15) | YES | | NULL| | | Lname | varchar(30) | YES | | NULL| | | Phone | varchar(30) | YES | | NULL| | | Height | int(4) | YES | | NULL| | | Sex| char(7) | YES | | NULL| | | Hx | text| YES | | NULL| | ++-+--+-+-+---+ 8 rows in set (0.00 sec) mysql describe Visit3; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Indx | int(4) | NO | PRI | NULL| auto_increment | | Site | varchar(6) | YES | | NULL|| | MedRec | int(6) | YES | | NULL|| | Notes | text | YES | | NULL|| | Weight | int(4) | YES | | NULL|| | BMI| decimal(3,1) | YES | | NULL|| | Date | date | YES | | NULL|| ++--+--+-+-++ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is First Try Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] RE: [PHP-DB] PHP Database Problems
Ethan, Some coding you are using would be helpful (as far as i am aware attachments are not support on the mailing list's) Gav -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: 02 May 2012 19:54 To: php-db-lists.php.net; php-general@lists.php.net Subject: [PHP-DB] PHP Database Problems have a database mysql describe Intake3; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Site | varchar(6) | NO | PRI | | | | MedRec | int(6) | NO | PRI | NULL| | | Fname | varchar(15) | YES | | NULL| | | Lname | varchar(30) | YES | | NULL| | | Phone | varchar(30) | YES | | NULL| | | Height | int(4) | YES | | NULL| | | Sex| char(7) | YES | | NULL| | | Hx | text| YES | | NULL| | ++-+--+-+-+---+ 8 rows in set (0.00 sec) mysql describe Visit3; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Indx | int(4) | NO | PRI | NULL| auto_increment | | Site | varchar(6) | YES | | NULL|| | MedRec | int(6) | YES | | NULL|| | Notes | text | YES | | NULL|| | Weight | int(4) | YES | | NULL|| | BMI| decimal(3,1) | YES | | NULL|| | Date | date | YES | | NULL|| ++--+--+-+-++ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is First Try Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP Database Problems
I do believe attachments are allowed. Looking back, I see that there have been messages sent to the list that had odt, php, and ini attachments On 05/02/2012 12:12 PM, Gavin Chalkley wrote: Ethan, Some coding you are using would be helpful (as far as i am aware attachments are not support on the mailing list's) Gav -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: 02 May 2012 19:54 To: php-db-lists.php.net; php-general@lists.php.net Subject: [PHP-DB] PHP Database Problems have a database mysql describe Intake3; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Site | varchar(6) | NO | PRI | | | | MedRec | int(6) | NO | PRI | NULL| | | Fname | varchar(15) | YES | | NULL| | | Lname | varchar(30) | YES | | NULL| | | Phone | varchar(30) | YES | | NULL| | | Height | int(4) | YES | | NULL| | | Sex| char(7) | YES | | NULL| | | Hx | text| YES | | NULL| | ++-+--+-+-+---+ 8 rows in set (0.00 sec) mysql describe Visit3; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Indx | int(4) | NO | PRI | NULL| auto_increment | | Site | varchar(6) | YES | | NULL|| | MedRec | int(6) | YES | | NULL|| | Notes | text | YES | | NULL|| | Weight | int(4) | YES | | NULL|| | BMI| decimal(3,1) | YES | | NULL|| | Date | date | YES | | NULL|| ++--+--+-+-++ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is First Try Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP Database Problems
Dear Ethan, It would be useful to see what code you are using. The syntax is: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value The data that you are required to enter is for Intake3. I am assuming that the only change is the History and/or Phone that you need to update since the Site, MedRec, Fname, Lname, Height and Sex should remain the same. Therefore I imagine the code should be something like mysql_query(UPDATE Intake3 SET History='Has been convinced by his friends that he is obese. Normal BMI = 23', Phone='325 563-4178' WHERE MedRec='1' LIMIT 1); Having said that I notice that you have two primary keys in Intake3. Not aware that a table can carry two. Terry On 2 May 2012 19:53, Ethan Rosenberg eth...@earthlink.net wrote: have a database mysql describe Intake3; ++-+--**+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--**+-+-+---+ | Site | varchar(6) | NO | PRI | | | | MedRec | int(6) | NO | PRI | NULL| | | Fname | varchar(15) | YES | | NULL| | | Lname | varchar(30) | YES | | NULL| | | Phone | varchar(30) | YES | | NULL| | | Height | int(4) | YES | | NULL| | | Sex| char(7) | YES | | NULL| | | Hx | text| YES | | NULL| | ++-+--**+-+-+---+ 8 rows in set (0.00 sec) mysql describe Visit3; ++--+-**-+-+-+**+ | Field | Type | Null | Key | Default | Extra | ++--+-**-+-+-+**+ | Indx | int(4) | NO | PRI | NULL| auto_increment | | Site | varchar(6) | YES | | NULL|| | MedRec | int(6) | YES | | NULL|| | Notes | text | YES | | NULL|| | Weight | int(4) | YES | | NULL|| | BMI| decimal(3,1) | YES | | NULL|| | Date | date | YES | | NULL|| ++--+-**-+-+-+**+ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is First Try Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- *Terry Ally* Twitter.com/terryally Facebook.com/terryally ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ To print or not to print this email is the environmentally-searching question! Which has the highest ecological cost? A sheet of paper or constantly switching on your computer and connecting to the Internet to read your email?
[PHP] PHP Database Problems -- Code Snippets
Dear list -
Sorry for the attachment. Here are code snippets ---
GET THE DATA FROM INTAKE3:
function handle_data()
{
global $cxn;
$query = select * from Intake3 where 1;
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' )
{
if ($_REQUEST['Sex'] === 0)
{
$sex = 'Male';
}
else
{
$sex = 'Female';
}
}
}
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'],
'Fname' = $_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] ,
'Height' = $_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
}
$result1 = mysqli_query($cxn, $query);
}
$num = mysqli_num_rows($result1);
if(($num = mysqli_num_rows($result1)) == 0)
{
?
br /br /centerbp style=color: red;
font-size:14pt; No Records Retrieved #1/center/b/style/p
?php
exit();
}
DISPLAY THE INPUT3 DATA:
THIS SEEMS TO BE THE ROUTINE THAT IS FAILING
centerbSearch Results/b/centerbr /
centertable border=4 cellpadding=5
cellspacing=55 rules=all frame=box
tr class=\heading\
thSite/th
thMedical Record/th
thFirst Name/th
thLast Name/th
thPhone/td
thHeight/td
thSex/td
thHistory/td
/tr
?php
while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH))
{
print_r($_POST);
global $MDRcheck;
$n1++;
echo br /n1 br /;echo $n1;
{
if (($n1 2) ($MDRcheck == $row1[1]))
{
echo 2== ;
echo $MDRcheck;
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
elseif (($n1 2) ($MDRcheck != $row1[1]))
{
echo 2!= ;
echo $MDRcheck;
continue;
}
elseif ($n1 == 2)
{
define( MDR , $row1[1]);
echo br /row1 br;echo $row1[1];
echo tr\n;
$_GLOBALS['mdr']= $row1[1];
$_POST['MedRec'] = $row1[1];
$MDRold = $_GLOBALS['mdr'];
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
}
}
?
SELECT AND DISPLAY DATA FROM VISIT3 DATABASE
?php
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND
(MedRec = $_GLOBALS[mdr]);
$result2 = mysqli_query($cxn, $query2);
$num = mysqli_num_rows($result2);
global $finished;
$finished = 0;
while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
global $finished;
echo tr\n;
echo td $row2[0] /td\n;
echo td $row2[1] /td\n;
echo td $row2[2] /td\n;
echo td $row2[3] /td\n;
echo td $row2[4] /td\n;
echo td $row2[5] /td\n;
echo td $row2[6] /td\n;
echo /tr\n;
}
echo /table;
ENTER MORE DATA:
function More_Data()
{
$decision = 5;
?
Do you Wish to Enter More Data?
form method=post action=
centerinput type=radio name=decision value=1 /Yes
input type=radio name=decision value=0 /No/centerbr /
centerinput type=submit value=Enter more Data //center
input type=hidden name=next_step value=step10 /
/form
?php
} //end function More_Data
switch ( @$_POST[next_step] )
{
case step10:
{
if (!isset($_POST['decision']))
{
$_POST['decision'] = 5;
}
if ($_POST['decision'] == 0)
{
exit();
}
if ($_POST['decision'] == 1)
{
;
echo form method=\post\ action=\\;
echo input type=\hidden\ name=\next_step\
value=\step4\ /;
echo enterbr /;
echo Medical Record: nbspinput type=\text\
name=\MedRec\ value=\ $_GLOBALS[mdr]\ /;
echo nbspnbsp Weight: input type=\decimal\
[PHP] Re: [PHP-DB] PHP Database Problems -- Code Snippets
I noticed the use of SQL concatenation like:
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' =
$_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] , 'Height' =
$_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
}
$result1 = mysqli_query($cxn, $query);
}
and like
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec =
$_GLOBALS[mdr]);
This is a SQL Injection security risk. There is a lot of material
on the web about this, e.g
https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet
I cannot strongly enough suggest you rewrite the app to mitigate
against this issue.
Also, set error_reporting = E_ALL E_STRICT in your php.ini file to
help you identify some of your other code issues.
Chris
--
christopher.jo...@oracle.com
http://twitter.com/#!/ghrd
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP Database Problems -- Code Snippets
On Wed, May 2, 2012 at 11:43 PM, Ethan Rosenberg eth...@earthlink.net wrote:
Dear list -
Sorry for the attachment. Here are code snippets ---
Ethan,
I don't want to sound rude, but it appears to me you don't have any
understanding of what you're doing. It might help if you understand
what the code is doing... Let me explain.
GET THE DATA FROM INTAKE3:
function handle_data()
{
global $cxn;
$query = select * from Intake3 where 1;
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' )
$_Request does not exists, you're looking for $_REQUEST. And why are
you mixing $_REQUEST and $_POST here?
{
if ($_REQUEST['Sex'] === 0)
{
$sex = 'Male';
}
else
{
$sex = 'Female';
}
}
}
What is the point of the handle_data function above? It doesn't do anything.
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' =
$_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] , 'Height'
= $_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
}
$result1 = mysqli_query($cxn, $query);
}
First, this will allow SQL injections, because you insert the values
directly from the browser.
Second, you should move the last line ($result1=...), outside of the
foreach loop, now you're executing the query multiple times.
Third, you should check if $result1 === FALSE, in case the query fails
$num = mysqli_num_rows($result1);
if(($num = mysqli_num_rows($result1)) == 0)
Doing the same thing twice?
{
?
br /br /centerbp style=color: red; font-size:14pt; No Records
Retrieved #1/center/b/style/p
?php
exit();
}
DISPLAY THE INPUT3 DATA:
THIS SEEMS TO BE THE ROUTINE THAT IS FAILING
centerbSearch Results/b/centerbr /
centertable border=4 cellpadding=5 cellspacing=55 rules=all
frame=box
tr class=\heading\
thSite/th
thMedical Record/th
thFirst Name/th
thLast Name/th
thPhone/td
thHeight/td
thSex/td
thHistory/td
/tr
?php
while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH))
{
print_r($_POST);
Doesn't really make sense to print $_POST here..
global $MDRcheck;
$n1++;
echo br /n1 br /;echo $n1;
{
if (($n1 2) ($MDRcheck == $row1[1]))
{
echo 2== ;
echo $MDRcheck;
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
elseif (($n1 2) ($MDRcheck != $row1[1]))
{
echo 2!= ;
echo $MDRcheck;
continue;
continue doesn't do anything here.
}
elseif ($n1 == 2)
{
define( MDR , $row1[1]);
echo br /row1 br;echo $row1[1];
echo tr\n;
$_GLOBALS['mdr']= $row1[1];
$_POST['MedRec'] = $row1[1];
You're not supposed to set variables in $_POST...
$MDRold = $_GLOBALS['mdr'];
It appears you want the old value of mdr, if so, then you should do
this before you set it again 2 lines above..
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
}
}
?
You say this routine is probably the one that is failing.. but what is
going wrong? And how the heck are we supposed to know what this
function should do?
SELECT AND DISPLAY DATA FROM VISIT3 DATABASE
?php
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec =
$_GLOBALS[mdr]);
You're using mdr as a constant here, this will generate a warning, but
sadly enough it works.
$result2 = mysqli_query($cxn, $query2);
You should check if $result2 === FALSE, in case the query fails.
$num = mysqli_num_rows($result2);
You're counting the rows here, but you don't do anything with the result?
Snip the rest of this crappy
Re: [PHP] PHP Database Problems
But I don't see any attachments in this message. On Thu, May 3, 2012 at 2:28 AM, Jim Lucas li...@cmsws.com wrote: I do believe attachments are allowed. Looking back, I see that there have been messages sent to the list that had odt, php, and ini attachments On 05/02/2012 12:12 PM, Gavin Chalkley wrote: Ethan, Some coding you are using would be helpful (as far as i am aware attachments are not support on the mailing list's) Gav -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: 02 May 2012 19:54 To: php-db-lists.php.net; php-general@lists.php.net Subject: [PHP-DB] PHP Database Problems have a database mysql describe Intake3; ++-+--**+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--**+-+-+---+ | Site | varchar(6) | NO | PRI | | | | MedRec | int(6) | NO | PRI | NULL| | | Fname | varchar(15) | YES | | NULL| | | Lname | varchar(30) | YES | | NULL| | | Phone | varchar(30) | YES | | NULL| | | Height | int(4) | YES | | NULL| | | Sex| char(7) | YES | | NULL| | | Hx | text| YES | | NULL| | ++-+--**+-+-+---+ 8 rows in set (0.00 sec) mysql describe Visit3; ++--+-**-+-+-+**+ | Field | Type | Null | Key | Default | Extra | ++--+-**-+-+-+**+ | Indx | int(4) | NO | PRI | NULL| auto_increment | | Site | varchar(6) | YES | | NULL|| | MedRec | int(6) | YES | | NULL|| | Notes | text | YES | | NULL|| | Weight | int(4) | YES | | NULL|| | BMI| decimal(3,1) | YES | | NULL|| | Date | date | YES | | NULL|| ++--+-**-+-+-+**+ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is First Try Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Duken Marga
Re: [PHP] PHP Database Problems -- Code Snippets
It is a good habbit to write the core code just for inserting/retrieving
data from database. It will reduce much of your code complexity. When you
see your code is working, you can continue to embed it with your visual
front-end or with your filter.
On Thu, May 3, 2012 at 4:43 AM, Ethan Rosenberg eth...@earthlink.netwrote:
Dear list -
Sorry for the attachment. Here are code snippets ---
GET THE DATA FROM INTAKE3:
function handle_data()
{
global $cxn;
$query = select * from Intake3 where 1;
if(isset($_Request['Sex']) trim($_POST['Sex']) != '' )
{
if ($_REQUEST['Sex'] === 0)
{
$sex = 'Male';
}
else
{
$sex = 'Female';
}
}
}
$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' =
$_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] ,
'Height' = $_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}
$query = select * from Intake3 where 1 ;
foreach ( $allowed_fields as $key = $val )
{
if ( (($val != '')) )
{
$query .= AND ($key = '$val') ;
}
$result1 = mysqli_query($cxn, $query);
}
$num = mysqli_num_rows($result1);
if(($num = mysqli_num_rows($result1)) == 0)
{
?
br /br /centerbp style=color: red; font-size:14pt; No
Records Retrieved #1/center/b/style/p
?php
exit();
}
DISPLAY THE INPUT3 DATA:
THIS SEEMS TO BE THE ROUTINE THAT IS FAILING
centerbSearch Results/b/centerbr /
centertable border=4 cellpadding=5 cellspacing=55 rules=all
frame=box
tr class=\heading\
thSite/th
thMedical Record/th
thFirst Name/th
thLast Name/th
thPhone/td
thHeight/td
thSex/td
thHistory/td
/tr
?php
while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH))
{
print_r($_POST);
global $MDRcheck;
$n1++;
echo br /n1 br /;echo $n1;
{
if (($n1 2) ($MDRcheck == $row1[1]))
{
echo 2== ;
echo $MDRcheck;
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
elseif (($n1 2) ($MDRcheck != $row1[1]))
{
echo 2!= ;
echo $MDRcheck;
continue;
}
elseif ($n1 == 2)
{
define( MDR , $row1[1]);
echo br /row1 br;echo $row1[1];
echo tr\n;
$_GLOBALS['mdr']= $row1[1];
$_POST['MedRec'] = $row1[1];
$MDRold = $_GLOBALS['mdr'];
echo td $row1[0] /td\n;
echo td $row1[1] /td\n;
echo td $row1[2] /td\n;
echo td $row1[3] /td\n;
echo td $row1[4] /td\n;
echo td $row1[5] /td\n;
echo td $row1[6] /td\n;
echo td $row1[7] /td\n;
echo /tr\n;
}
}
}
?
SELECT AND DISPLAY DATA FROM VISIT3 DATABASE
?php
$query2 = select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec
= $_GLOBALS[mdr]);
$result2 = mysqli_query($cxn, $query2);
$num = mysqli_num_rows($result2);
global $finished;
$finished = 0;
while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
global $finished;
echo tr\n;
echo td $row2[0] /td\n;
echo td $row2[1] /td\n;
echo td $row2[2] /td\n;
echo td $row2[3] /td\n;
echo td $row2[4] /td\n;
echo td $row2[5] /td\n;
echo td $row2[6] /td\n;
echo /tr\n;
}
echo /table;
ENTER MORE DATA:
function More_Data()
{
$decision = 5;
?
Do you Wish to Enter More Data?
form method=post action=
centerinput type=radio name=decision value=1 /Yes input
type=radio name=decision value=0 /No/centerbr /
centerinput type=submit value=Enter more Data //center
input type=hidden name=next_step value=step10 /
/form
?php
} //end function More_Data
switch ( @$_POST[next_step] )
{
case step10:
{
if (!isset($_POST['decision']))
{
$_POST['decision'] = 5;
}
if ($_POST['decision'] == 0)
{
exit();
}
if ($_POST['decision']
Re: [PHP] PHP Database Problems
On 5/2/2012 4:28 PM, Duken Marga wrote: But I don't see any attachments in this message. This was in the first email of this thread. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] database design in a survey/poll system
I plan to design a small survey/poll system similar to polldaddyhttp://polldaddy.com/ . And I have some confusion in designing the database for the multiple/single choice questions. Of course, it is possible to use one table to store the question title and another table to store the choice item(one record for each choice item). My main concern is that whether we can place the choices together in the same table(and in one ) with the question title. I guess it can be faster to read from one table than reading from two table. And my idea is to use a delimiter to separate the choices. And the handling of the choices are done in the php script. But what delimiter should be used? Can anyone help? Thanks!
Re: [PHP] database design in a survey/poll system
On Wed, Nov 17, 2010 at 8:42 AM, 肖晗 xiaohan2...@gmail.com wrote: I plan to design a small survey/poll system similar to polldaddyhttp://polldaddy.com/ . And I have some confusion in designing the database for the multiple/single choice questions. Of course, it is possible to use one table to store the question title and another table to store the choice item(one record for each choice item). My main concern is that whether we can place the choices together in the same table(and in one ) with the question title. I guess it can be faster to read from one table than reading from two table. And my idea is to use a delimiter to separate the choices. And the handling of the choices are done in the php script. But what delimiter should be used? Can anyone help? Thanks! When you denormalize like this, it's important to make sure that you don't need to perform more complex queries on the items (sorting, max, min, etc.), as most DB's will outperform PHP for this type of work even if it does require one or more table relationships. That said, when the query needs are simple enough to merit this type of approach, I tend to JSON-encode the data ( http://php.net/manual/en/function.json-encode.php), leaving a simple JSON-decode operation (http://www.php.net/manual/en/function.json-decode.php) to get the queried data back into PHP form. Adam -- Nephtali: PHP web framework that functions beautifully http://nephtaliproject.com
Re: [PHP] database design in a survey/poll system
On Nov 17, 2010, at 7:42 AM, 肖晗 wrote: I plan to design a small survey/poll system similar to polldaddyhttp://polldaddy.com/ . And I have some confusion in designing the database for the multiple/ single choice questions. Of course, it is possible to use one table to store the question title and another table to store the choice item(one record for each choice item). As you have a many-to-one relationship of answers to questions, two tables would be necessary in a normalized database. My main concern is that whether we can place the choices together in the same table(and in one ) with the question title. I guess it can be faster to read from one table than reading from two table. It is possible, but really, why bother? It's not a very time consuming function whether you join two tables or read from one and end up parsing the results. String parsing can be expensive, too. And my idea is to use a delimiter to separate the choices. And the handling of the choices are done in the php script. But what delimiter should be used? You can easily choose any character and just make sure your responses never include that character, or escape it somehow (eg. via \ ) Can anyone help? Thanks! I really don't think you gain much, if anything, by having a single table in this instance. The retrieval is trivial for the sql engine, vs creating parsing code in PHP which may be trouble-prone or convoluted. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
Thanks for the replies, they have been most enlightening. :)
Re: [PHP] Database Administration
On 22 September 2010 21:40, Bastien Koert phps...@gmail.com wrote: Not at all. What I would suggest is that you create a separate mysql user that is used exclusively by the script to do the create stuff. The regular application user account should not have those privileges at all. I'm not actually that familiar with DB admin to that extent. I have either app users with lock+crud on specific databases, or root. As a an aside, would you know if there is a level of permissions for a user between app and root that would be 'sensibly secure' (it will be MySQL 5)? Another option, if immediate response is not required, is to save this data into the system for a cron script with another user account to run. This was sort of my first instinct. I ponder writing a small daemon/cron that queries a database table (client list) and does all the 'build' bits. The main issue with cron is that the users would want a fairly immediate response. Seconds is acceptable, but a 5 minute cron might be too slow. Is there a reason for you not to place all the data in one DB and just separate them out based on user id, to ensure they only see their own data For legal reasons. Each client must have separate data. I need to be able to box up all the client data (containing multiple app instances) and be 100% sure that I am giving them all their data and nobody else's. On 23 September 2010 18:04, tedd tedd.sperl...@gmail.com wrote: No, but from what you've said, I don't think the end user must have privileges and the ability to create a database and tables. It sounds more like allowing the user to set up his own admin for acceptable users -- there's a big difference. So, what you need to define is what the client and his users want to do. From that, we can determine what they need. Depending on what you mean by 'the client', all the client side things are fine :) The web front-end I am working on here is for internal use only. To allow non-technical people to set up clients and their apps. The more I look into this, the more I am leaning towards some shell scripts for client management, invoking them by cron. Then if an immediate response is needed someone technical will have to manually run the cron job. It looks like the law of diminishing returns for me to build something really usable.
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 6:19 AM, Tom Barrett t...@miramedia.co.uk wrote: [snip] I'm not actually that familiar with DB admin to that extent. I have either app users with lock+crud on specific databases, or root. As a an aside, would you know if there is a level of permissions for a user between app and root that would be 'sensibly secure' (it will be MySQL 5)? It depends on the app, but phrases like 'sensibly secure' raise caution flags for me. I tend to go with the principle of least privilege. Where I currently work, the admin functions for a web application are usually on an intranet site that is completely separate from the public site. Because of this, I have a different database user for each site. In this case, these are database-only logins unrelated in any way to the actual machine account used by the web servers. On our newer development, nearly all table access is managed strictly through stored procedures (we use SQL Server, but the same would work for MySQL if you were so inclined), and each database user is only granted execute permission on the specific procedures necessary for that role. The only time we grant access directly to a table is in cases where we just can't get a procedure to do what we need efficiently or effectively. And, in those cases where I do need to grant access to a table, I grant permission to only the columns/operations necessary for that user. If I encountered a case where I needed to allow a user to make schema changes as you mentioned in your original post, I would create a totally separate account -- again with no more permission than necessary for its intended task. Depending on the needs of the application, I'd decide whether that account was used by the web server or via a script scheduled to execute at intervals as several others have suggested in this thread. I've not tried this, but you could probably write the logic needed to create the database objects into a stored procedure. Then, you might only need to grant permission to that procedure and not grant permission to CREATE/ALTER anything. That would pretty well guarantee that the only objects created are the ones you intended. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 11:19 AM +0100 9/24/10, Tom Barrett wrote: On 22 September 2010 21:40, Bastien Koert phps...@gmail.com wrote: Not at all. What I would suggest is that you create a separate mysql user that is used exclusively by the script to do the create stuff. The regular application user account should not have those privileges at all. I'm not actually that familiar with DB admin to that extent. I have either app users with lock+crud on specific databases, or root. As a an aside, would you know if there is a level of permissions for a user between app and root that would be 'sensibly secure' (it will be MySQL 5)? Another option, if immediate response is not required, is to save this data into the system for a cron script with another user account to run. This was sort of my first instinct. I ponder writing a small daemon/cron that queries a database table (client list) and does all the 'build' bits. The main issue with cron is that the users would want a fairly immediate response. Seconds is acceptable, but a 5 minute cron might be too slow. Is there a reason for you not to place all the data in one DB and just separate them out based on user id, to ensure they only see their own data For legal reasons. Each client must have separate data. I need to be able to box up all the client data (containing multiple app instances) and be 100% sure that I am giving them all their data and nobody else's. On 23 September 2010 18:04, tedd tedd.sperl...@gmail.com wrote: No, but from what you've said, I don't think the end user must have privileges and the ability to create a database and tables. It sounds more like allowing the user to set up his own admin for acceptable users -- there's a big difference. So, what you need to define is what the client and his users want to do. From that, we can determine what they need. Depending on what you mean by 'the client', all the client side things are fine :) The web front-end I am working on here is for internal use only. To allow non-technical people to set up clients and their apps. The more I look into this, the more I am leaning towards some shell scripts for client management, invoking them by cron. Then if an immediate response is needed someone technical will have to manually run the cron job. It looks like the law of diminishing returns for me to build something really usable. The more I hear, the more confused I get. I still don't understand what your client is going to do? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 2:05 PM, tedd tedd.sperl...@gmail.com wrote: At 11:19 AM +0100 9/24/10, Tom Barrett wrote: On 22 September 2010 21:40, Bastien Koert phps...@gmail.com wrote: Not at all. What I would suggest is that you create a separate mysql user that is used exclusively by the script to do the create stuff. The regular application user account should not have those privileges at all. I'm not actually that familiar with DB admin to that extent. I have either app users with lock+crud on specific databases, or root. As a an aside, would you know if there is a level of permissions for a user between app and root that would be 'sensibly secure' (it will be MySQL 5)? Another option, if immediate response is not required, is to save this data into the system for a cron script with another user account to run. This was sort of my first instinct. I ponder writing a small daemon/cron that queries a database table (client list) and does all the 'build' bits. The main issue with cron is that the users would want a fairly immediate response. Seconds is acceptable, but a 5 minute cron might be too slow. Is there a reason for you not to place all the data in one DB and just separate them out based on user id, to ensure they only see their own data For legal reasons. Each client must have separate data. I need to be able to box up all the client data (containing multiple app instances) and be 100% sure that I am giving them all their data and nobody else's. On 23 September 2010 18:04, tedd tedd.sperl...@gmail.com wrote: No, but from what you've said, I don't think the end user must have privileges and the ability to create a database and tables. It sounds more like allowing the user to set up his own admin for acceptable users -- there's a big difference. So, what you need to define is what the client and his users want to do. From that, we can determine what they need. Depending on what you mean by 'the client', all the client side things are fine :) The web front-end I am working on here is for internal use only. To allow non-technical people to set up clients and their apps. The more I look into this, the more I am leaning towards some shell scripts for client management, invoking them by cron. Then if an immediate response is needed someone technical will have to manually run the cron job. It looks like the law of diminishing returns for me to build something really usable. The more I hear, the more confused I get. I still don't understand what your client is going to do? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating new systems for their clients mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. So, what specifically are these non-techie users going to do? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 2:26 PM, tedd tedd.sperl...@gmail.com wrote: At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating new systems for their clients mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. So, what specifically are these non-techie users going to do? Cheers, tedd -- --- http://sperling.com/ Create a DB schema, create and populate tables. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 2:36 PM -0400 9/24/10, Bastien Koert wrote: On Fri, Sep 24, 2010 at 2:26 PM, tedd tedd.sperl...@gmail.com wrote: At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating new systems for their clients mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. So, what specifically are these non-techie users going to do? Create a DB schema, create and populate tables. Creating a DB schema is not for non-techies -- you really need to know what you are doing to do this. But we all live with what we create. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database Administration
From: tedd At 2:36 PM -0400 9/24/10, Bastien Koert wrote: On Fri, Sep 24, 2010 at 2:26 PM, tedd tedd.sperl...@gmail.com wrote: At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating new systems for their clients mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. So, what specifically are these non-techie users going to do? Create a DB schema, create and populate tables. Creating a DB schema is not for non-techies -- you really need to know what you are doing to do this. But we all live with what we create. I suspect he actually means create a new table using a predefined schema. But unfortunately, he doesn't appear to know enough about the problem to be able to explain it. He is either in way over his depth, or hasn't done a very good job of defining his requirements. Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 3:50 PM, Bob McConnell r...@cbord.com wrote: From: tedd At 2:36 PM -0400 9/24/10, Bastien Koert wrote: On Fri, Sep 24, 2010 at 2:26 PM, tedd tedd.sperl...@gmail.com wrote: At 2:09 PM -0400 9/24/10, Bastien Koert wrote: �...@tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating new systems for their clients mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. So, what specifically are these non-techie users going to do? Create a DB schema, create and populate tables. Creating a DB schema is not for non-techies -- you really need to know what you are doing to do this. But we all live with what we create. I suspect he actually means create a new table using a predefined schema. But unfortunately, he doesn't appear to know enough about the problem to be able to explain it. He is either in way over his depth, or hasn't done a very good job of defining his requirements. Bob McConnell The OP mentioned that each new client needed their own DB so that is how I took it. Having exactly the exact same tables in the structure with different names is just bad practice to. That just screams creating a multi-tenant DB. At work we do create each DB as a clone of a master table, but it is done manually and thankfully not that often. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 9:35 PM +0100 9/22/10, Tom Barrett wrote: Hmm.. I am familiar with PMA. I would for the purpose of this project consider it too technical for the target user base. The point is to create a GUI layer that would manage these things. For example, the 'add client' screen would ask for four things; name, description, username and password. Then behind the scenes a database would be created, the user created, permissions granted and a pre-configure set of tables built (and populated). My reservations come from security issues (which, as an aside, are also discussed about PMA), allowing a normal user account CREATE and GRANT on the database. Maybe I'm being too fuddy-duddy cautious. Tom: No, but from what you've said, I don't think the end user must have privileges and the ability to create a database and tables. It sounds more like allowing the user to set up his own admin for acceptable users -- there's a big difference. So, what you need to define is what the client and his users want to do. From that, we can determine what they need. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
Hmm.. I am familiar with PMA. I would for the purpose of this project consider it too technical for the target user base. The point is to create a GUI layer that would manage these things. For example, the 'add client' screen would ask for four things; name, description, username and password. Then behind the scenes a database would be created, the user created, permissions granted and a pre-configure set of tables built (and populated). My reservations come from security issues (which, as an aside, are also discussed about PMA), allowing a normal user account CREATE and GRANT on the database. Maybe I'm being too fuddy-duddy cautious.
Re: [PHP] Database Administration
On Wed, Sep 22, 2010 at 4:35 PM, Tom Barrett t...@miramedia.co.uk wrote: Hmm.. I am familiar with PMA. I would for the purpose of this project consider it too technical for the target user base. The point is to create a GUI layer that would manage these things. For example, the 'add client' screen would ask for four things; name, description, username and password. Then behind the scenes a database would be created, the user created, permissions granted and a pre-configure set of tables built (and populated). My reservations come from security issues (which, as an aside, are also discussed about PMA), allowing a normal user account CREATE and GRANT on the database. Maybe I'm being too fuddy-duddy cautious. Not at all. What I would suggest is that you create a separate mysql user that is used exclusively by the script to do the create stuff. The regular application user account should not have those privileges at all. Another option, if immediate response is not required, is to save this data into the system for a cron script with another user account to run. Is there a reason for you not to place all the data in one DB and just separate them out based on user id, to ensure they only see their own data? -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database Administration
Hi I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)?
Re: [PHP] Database Administration
On 21 September 2010 11:48, Tom Barrett t...@miramedia.co.uk wrote: Hi I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? Seeing as all the extra stuff you need is just plain sql commands, I don't see the problem as such. You just need to make sure access to the tool is done right (i.e. a user that can create/destroy databases needs to be aware of this and you need to restrict the access to those specific persons). Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database Administration
Possible. Google phpmyadmin I believe it is completely written in PHP and does complete database administration (has some weaknesses on stored procedures though in my view) Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: Tom Barrett [mailto:t...@miramedia.co.uk] Sent: 21 September 2010 11:48 AM To: PHP General List Subject: [PHP] Database Administration Hi I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database Administration
[snip] I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? [/snip] Have you thought about using phpMyAdmin? http://www.phpmyadmin.net/home_page/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 10:48 AM +0100 9/21/10, Tom Barrett wrote: Hi I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? I'm not sure as to what you need, but for me I do all my database creation in phpMyAdmin. From there I populate with php. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP database interface layer
Hi everyone, I've built a fairly large normalized database schema for a project. This is fun for me as I like thinking about how everything is interconnected. Foreign keys are all set up, many-to-many tables are go, etc, and so on. But now it's time to create an interface between that database and the website using PHP. I've searched the web and this is obviously a very common problem with many solutions, but I can't help but feel that all of the logic I've built into the database is worth nothing once I start coding. I found this article/presentation that essentially sums up my frustration: http://mag-sol.com/talks/lpm/2006/orm/. Up until now I've been working on smaller projects with only a few tables that don't really relate to each other, and have found this tool (http://www.ricocheting.com/code/php/mysql-database-class-wrapper) very handy for the reasons he explains, but I'm looking for something a little different. I've looked at RedBean and it seems pretty handy, but it also requires me to redefine all of the foreign keys I've already defined, which is annoying. Any hope of something like // Get company where name='Widgets Inc.' or id=1 or $company = $db-get_company(name='Widgets Inc.'); // Get all clients joined to the company $clients = $company-get_clients(); // Get all projects joined to the client $projects = $clients-get_projects(); ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP database interface layer
On 22 July 2010 15:35, Marc Guay marc.g...@gmail.com wrote: Hi everyone, I've built a fairly large normalized database schema for a project. This is fun for me as I like thinking about how everything is interconnected. Foreign keys are all set up, many-to-many tables are go, etc, and so on. But now it's time to create an interface between that database and the website using PHP. I've searched the web and this is obviously a very common problem with many solutions, but I can't help but feel that all of the logic I've built into the database is worth nothing once I start coding. I found this article/presentation that essentially sums up my frustration: http://mag-sol.com/talks/lpm/2006/orm/. Up until now I've been working on smaller projects with only a few tables that don't really relate to each other, and have found this tool (http://www.ricocheting.com/code/php/mysql-database-class-wrapper) very handy for the reasons he explains, but I'm looking for something a little different. I've looked at RedBean and it seems pretty handy, but it also requires me to redefine all of the foreign keys I've already defined, which is annoying. Any hope of something like // Get company where name='Widgets Inc.' or id=1 or $company = $db-get_company(name='Widgets Inc.'); // Get all clients joined to the company $clients = $company-get_clients(); // Get all projects joined to the client $projects = $clients-get_projects(); ? Have you looked at things like Doctrine2? Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP database interface layer
On Thu, Jul 22, 2010 at 7:35 AM, Marc Guay marc.g...@gmail.com wrote: Hi everyone, I've built a fairly large normalized database schema for a project. This is fun for me as I like thinking about how everything is interconnected. Foreign keys are all set up, many-to-many tables are go, etc, and so on. But now it's time to create an interface between that database and the website using PHP. I've searched the web and this is obviously a very common problem with many solutions, but I can't help but feel that all of the logic I've built into the database is worth nothing once I start coding. I found this article/presentation that essentially sums up my frustration: http://mag-sol.com/talks/lpm/2006/orm/. Up until now I've been working on smaller projects with only a few tables that don't really relate to each other, and have found this tool (http://www.ricocheting.com/code/php/mysql-database-class-wrapper) very handy for the reasons he explains, but I'm looking for something a little different. I've looked at RedBean and it seems pretty handy, but it also requires me to redefine all of the foreign keys I've already defined, which is annoying. Any hope of something like // Get company where name='Widgets Inc.' or id=1 or $company = $db-get_company(name='Widgets Inc.'); // Get all clients joined to the company $clients = $company-get_clients(); // Get all projects joined to the client $projects = $clients-get_projects(); ? i recommend propel http://www.propelorm.org/ -nathan
Re: [PHP] PHP database interface layer
i recommend propel http://www.propelorm.org/ This looks hopeful. I'd checked it out before but for some reason lumped it in with all of the other half-baked tools that didn't do what I wanted, but even that basic example seems to cover most of what I want. Thanks for the suggestions. Marc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP database interface layer
i recommend propel http://www.propelorm.org/ Holy Moses that thing is a monster. It requires installing extra libraries (Phing) in order to create an XML schema reverse-engineered from my existing database. The code looks simple enough but that installation is brutal. Any other suggestions? I'm thinking of sticking with good ol' hand coding and a few helper classes. Marc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP database interface layer
On Jul 22, 2010, at 3:14 PM, Marc Guay wrote: i recommend propel http://www.propelorm.org/ Holy Moses that thing is a monster. It requires installing extra libraries (Phing) in order to create an XML schema reverse-engineered from my existing database. The code looks simple enough but that installation is brutal. Any other suggestions? I'm thinking of sticking with good ol' hand coding and a few helper classes. Marc I kind of had the same reaction when I saw it! I started playing around with it and realized that if I ever make any database changes I'm going to have to regenerate everything! Seems a bit cumbersome to me but I'm an good ol' hand coding and a few helper classes programmer myself! I wound up creating a very light-weight yet flexible framework that has served me well over the past few years. I've been thinking about releasing it but it doesn't follow the traditional model of most of the MVC frameworks I've seen. Take care, Floyd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP database interface layer
On Thu, Jul 22, 2010 at 1:14 PM, Marc Guay marc.g...@gmail.com wrote: i recommend propel http://www.propelorm.org/ Holy Moses that thing is a monster. It requires installing extra libraries (Phing) in order to create an XML schema reverse-engineered from my existing database. The code looks simple enough but that installation is brutal. Any other suggestions? I'm thinking of sticking with good ol' hand coding and a few helper classes. Marc so you spend a little time up front to sav tons of coding time in the future... change your schema; re-generate the base layer.., o yeah, and it preserves custom logic too (upon regeneration), since that resides in sub classes. i used to hand write db wrapper classes but found it tedious and tended to result in a lack of cohesion through the classes themselves, not to mention being error prone. -nathan
Re: [PHP] PHP database interface layer
On 22 July 2010 21:14, Marc Guay marc.g...@gmail.com wrote: i recommend propel http://www.propelorm.org/ Holy Moses that thing is a monster. It requires installing extra libraries (Phing) in order to create an XML schema reverse-engineered from my existing database. The code looks simple enough but that installation is brutal. Any other suggestions? I'm thinking of sticking with good ol' hand coding and a few helper classes. Let me repeat myself: did you have a look at Doctrine2? Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP database interface layer
Let me repeat myself: did you have a look at Doctrine2? Hi Peter, I didn't mean to ignore your suggestion, I just got extremely overwealmed by the Doctrine website and didn't even have a response. I get the impression that, like Zend and others, learning how to install and use that would take longer than my 'little' website is worth. Maybe someday when I'm working for a more patient employer who's willing to pay me to learn. Insert laughter here. Marc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP database interface layer
On 07/22/2010 08:35 AM, Marc Guay wrote: Hi everyone, I've built a fairly large normalized database schema for a project. This is fun for me as I like thinking about how everything is interconnected. Foreign keys are all set up, many-to-many tables are go, etc, and so on. But now it's time to create an interface between that database and the website using PHP. I've searched the web and this is obviously a very common problem with many solutions, but I can't help but feel that all of the logic I've built into the database is worth nothing once I start coding. I found this article/presentation that essentially sums up my frustration: http://mag-sol.com/talks/lpm/2006/orm/. Up until now I've been working on smaller projects with only a few tables that don't really relate to each other, and have found this tool (http://www.ricocheting.com/code/php/mysql-database-class-wrapper) very handy for the reasons he explains, but I'm looking for something a little different. I've looked at RedBean and it seems pretty handy, but it also requires me to redefine all of the foreign keys I've already defined, which is annoying. Any hope of something like // Get company where name='Widgets Inc.' or id=1 or $company = $db-get_company(name='Widgets Inc.'); // Get all clients joined to the company $clients = $company-get_clients(); // Get all projects joined to the client $projects = $clients-get_projects(); ? You may not want a full fledged framework, but I would recommend CakePHP. Now they have naming conventions for tables/columns, that if you follow, makes it sooo easy, but if you already have your schema built it can use that just as easily. Just run the cake script, enter db details, it will enumerate tables and columns and ask which are related by which keys and even let you enter validation rules etc. It then builds a skeleton app with all the models for your schema and will even generate test. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database vs. Array
I have a script that connects to an external database to process about 4,000 records. Each record needs to be operated on pretty heavily, and the script overall takes about half an hour to execute. We've hit a wall where the script's memory usage exceeds the amount allocated to PHP. I've increased the allotted memory to 32MB, but that's not ideal for our purposes. So my thought is, would it be more efficient, memory-wise, to read the database entries into an array and process the array records, rather than maintain the database connection for the entire run of the script. This is not an issue I've come across before, so any thoughts would be much appreciated. Thanks in advance. -- Richard S. Crawford (rich...@underpope.com) http://www.underpope.com Publisher and Editor in Chief, Daikaijuzine (http://www.daikaijuzine.com)
Re: [PHP] Database vs. Array
What I usually do is to pull a limited set of records ( like 10 or 50 ) and the do the operations on them, update a column in that table to mark them completed and use JavaScript to reload the page and pull the next set out where that flag field is null. No memory issue, no need to large timeouts and it's self recovering. Bastien On Tuesday, March 16, 2010, Richard S. Crawford rich...@underpope.com wrote: I have a script that connects to an external database to process about 4,000 records. Each record needs to be operated on pretty heavily, and the script overall takes about half an hour to execute. We've hit a wall where the script's memory usage exceeds the amount allocated to PHP. I've increased the allotted memory to 32MB, but that's not ideal for our purposes. So my thought is, would it be more efficient, memory-wise, to read the database entries into an array and process the array records, rather than maintain the database connection for the entire run of the script. This is not an issue I've come across before, so any thoughts would be much appreciated. Thanks in advance. -- Richard S. Crawford (rich...@underpope.com) http://www.underpope.com Publisher and Editor in Chief, Daikaijuzine (http://www.daikaijuzine.com) -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database vs. Array
Maybe you want to optimize your script first, and I don't think read entire data set into array would save you much time. Don't create new variables when its unnecessary, cache data when its necessary, try memcached, and I think heavy php cli scripts are always likely to resume a lot of resource, I think GC of php is not so reliable... or maybe I don't know how to use it. On 3/16/2010 7:13 PM, Richard S. Crawford wrote: I have a script that connects to an external database to process about 4,000 records. Each record needs to be operated on pretty heavily, and the script overall takes about half an hour to execute. We've hit a wall where the script's memory usage exceeds the amount allocated to PHP. I've increased the allotted memory to 32MB, but that's not ideal for our purposes. So my thought is, would it be more efficient, memory-wise, to read the database entries into an array and process the array records, rather than maintain the database connection for the entire run of the script. This is not an issue I've come across before, so any thoughts would be much appreciated. Thanks in advance. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] database credentials with ini_set() question?
I know that phpinfo() will show the pass and username as plain text, but other than that what are the security reasons for not using ini_set() for database credentials? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database design
Hi all. I know this is not strictly a PHP question but the code will be written in PHP, and I figure the folks here will be well versed in the questions I raise. Please feel free to contact me off the list if appropriate. I need some assistance with database design for a project I'm coding in PHP. I'm willing to pay for the advice, since I think it will be a bit complex. I plan to use MySQLi If anybody feels they can assist, or can point elsewhere please feel free to contact me off list, or reply to the list if you think appropriate. Thanks, Angus
Re: [PHP] Database design
On Thu, Feb 25, 2010 at 03:37:38PM +1000, Angus Mann wrote: Hi all. I know this is not strictly a PHP question but the code will be written in PHP, and I figure the folks here will be well versed in the questions I raise. Please feel free to contact me off the list if appropriate. I need some assistance with database design for a project I'm coding in PHP. I'm willing to pay for the advice, since I think it will be a bit complex. I plan to use MySQLi Not sure why you want to use MySQLi, but I would suggest using PDO functions instead. Either way, I'd design a wrapper class around whatever library you use, to simplify error trapping, make the interface simpler and more orthogonal, and improve portability. I've never understood why they didn't provide such an overall wrapper class for the PDO library. Having three separate PDO classes only complicates the interface to the library. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Ashley Sheridan wrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! ADOdb handles SEQUENCE correctly across all databases. Since MySQL does not understand SEQUENCE or GENERATOR, ADOdb simulates it with a dummy table which autoincrements and gets around the problem. Then one can use a secure generic GetID ;) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] database abstraction layer
Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Regards Lars Nielsen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lars Nielsen wrote: Is it save to assume that I can use the same SQL, or should i make some exceptions? Standard SQL should work across all SQL servers with only a few exceptions (for example, MySQL doesn't support full outer joins.) Anything that has to do with server administration, however, such as dealing with users and permissions, will be unique to the db engine. James -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lars Nielsen wrote: Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Is there a reason why you want to write your own instead of using something like Pear MDB2? With Pear MDB2 - if your SQL syntax is database specific it will work in the specific database but MDB2 will not try to port a specialized SQL string to another database. It will port some features to some databases, IE if you use the MDB2 facilities for prepared statements (highly recommended) and the target database does not support prepared statements, it will emulate them (I think, haven't tried, that's what I recall reading anyway) but for your actual SQL syntax it is best to stick to standard SQL. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lars Nielsen wrote: Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Simple SQL is almost identical. But there are many of the more advanced functions that have major differences. Check out ADOdb for an existing abstraction layer that handles a lot of them. http://adodb.sourceforge.net/ -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
i'm a fan of adodb.sf.net, which i've used with both postgresql and mysql. On Tue, Feb 2, 2010 at 9:23 PM, Lars Nielsen l...@mit-web.dk wrote: Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Regards Lars Nielsen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Tue, Feb 02, 2010 at 09:23:47PM +0100, Lars Nielsen wrote: Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Regards Lars Nielsen Quote of values is different between PostgreSQL and MySQL. I would suggest you do a wrapper class around the PDO classes, which will take care of quoting, etc. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] database abstraction layer
-Original Message-
From: Lars Nielsen [mailto:l...@mit-web.dk]
Sent: Tuesday, February 02, 2010 12:24 PM
To: php-general@lists.php.net
Subject: [PHP] database abstraction layer
Hi List
I am trying to make a Database Abstraction Layer so I can which the DB
of my application between MySQL and Postgresql. I have been looking at
the way phpBB does it, and it seems that it is only then php-functions
which are different. The SQL seems to be the same.
Is it save to assume that I can use the same SQL, or should i
make some
exceptions?
Regards
Lars Nielsen
There are differences in the actual schema between mySQL and Postgress.
At least there were a few years back when we looked at converting. In the
end, we decided it was too much hassle to switch all our code and database
tables, so just coughed up the licensing for mysql (we were shipping mysql
on our appliance).
So, before you jump into writing all this code, I would first try to make
your app run in postgress and find out about which mySQL statements are
'extensions' to ANSI standard. Case sensitivity was a huge issue for us, as
in one of those RDBMS was very particular about it. There were some other
issues that I can't remember ATM, but perhaps they've been addressed by
now.
One thing I would maybe suggest is (what I do), write a wrapper around your
wrapper -- AKA Double Bag It. :)
Here at Panasonic Avionics (PAC) we use the PEAR::DB class (the really old
version) since we have to interface with mySQL, SQL Server, Oracle (two
versions). That's where PEAR::DB comes in. However, it's very crude and you
have a lot of redundant code in every page. Like this:
http://pear.php.net/manual/en/package.database.db.intro-fetch.php
You always have to open a connection, test for errors, do the query, test
for errors, fetch the rows, etc..
When I came on board a year ago, I put an end to that micky mouse crap. I
wrote a nice db.inc.php wrapper that handles all that sort of thing, and
then pumps it up like it's on steroids. I added auto-reconnect in case the
connection dropped. I added color-coded SQL output with substitution for
the '?'. I added a last_insert_it() routine which is proprietary to mySQL
BTW (speaking of incompatibilities). I added routines to get an Enum
column, or to get a simple array pairing, etc. It can even force reads from
slave and writes to master! It pretty much kicks ass.
Just simply do this:
$myfoo = sql_query('agis_core', 'SELECT * FROM foo WHERE bar = ?', $bar);
All the minutia is handled for you and $myfoo is now an array of your
results. :)
So, now we code using my wrapper and should we want to switch out the DBAL
later to a more modern one, we just change OUR wrapper calls. There is
minimal overhead, and the pros FAR outweigh any cons.
I've attached it here.
We have another config.inc.php that has the DB settings for each
DEV/TEST/PROD master/slave servers (as they are all different accounts for
security reasons. So just make one with entries like this:
// AGISCore Database DEV Master mySQL:
$global_db_dsn_agis_core_master = array(
'phptype' = 'mysql',
'username' = 'RWMaster',
'password' = 'rwmaster',
'hostspec' = '10.10.10.2:3306',
'database' = 'agis_core',
'persistent' = TRUE
);
// AGISCore Database DEV Slave mySQL:
$global_db_dsn_agis_core_slave = array(
'phptype' = 'mysql',
'username' = 'ROSlave',
'password' = 'roslave',
'hostspec' = '10.10.10.3:3306',
'database' = 'agis_core',
'persistent' = TRUE
);
$GLOBALS['DB_CONNECTIONS'] is a singleton (sans the class overhead) so that
you always get the same handle for each database call and don't spawn new
ones each time. Nice. :)
?php
/**
* All of the database wrapper functions
*
* This is a wrapper around the PEAR::DB class. It provides many enhancements
including
* a singleton for database handle connections, retries for connections,
debugging with ? substitutions,
* handy routines to populate arrays, select boxes, IN() statements, etc. It
can do SQL timing profiling.
* There are routines for INSERT and UPDATE by simply passing in an array of
key/value pairs.
*
* Confidential property of Panasonic Avionics. Do not copy or distribute.
* @copyright 2006-2010 Panasonic Avionics. All rights reserved.
* @categoryCategoryName
* @package PackageName
* @see
* @since DART2
* @author Daevid Vincent daevid.vinc...@panasonic.aero
* @dateCreated: 2009-01-20
* @version CVS: $Id: db.inc.php,v 1.39 2010/01/29 01:35:30 vincentd Exp $
*/
require_once '/usr/share/php/DB.php';
$SQL_OPTION['noHTML']= false;
$SQL_OPTION['fullQuery'] = true;
$SQL_OPTION['useLogger'] = false;
$SQL_OPTION['profile'] = 0;
$SQL_OPTION['debug'] = false;
$SQL_OPTION['outfile'] = false; //set this to a filename, and use $show_sql
in your queries and they'll go to this file.
$GLOBALS
Re: [PHP] database abstraction layer
On Tue, Feb 02, 2010 at 01:15:22PM -0800, Daevid Vincent wrote:
-Original Message-
From: Lars Nielsen [mailto:l...@mit-web.dk]
Sent: Tuesday, February 02, 2010 12:24 PM
To: php-general@lists.php.net
Subject: [PHP] database abstraction layer
Hi List
I am trying to make a Database Abstraction Layer so I can which the DB
of my application between MySQL and Postgresql. I have been looking at
the way phpBB does it, and it seems that it is only then php-functions
which are different. The SQL seems to be the same.
Is it save to assume that I can use the same SQL, or should i
make some
exceptions?
Regards
Lars Nielsen
There are differences in the actual schema between mySQL and Postgress.
At least there were a few years back when we looked at converting. In the
end, we decided it was too much hassle to switch all our code and database
tables, so just coughed up the licensing for mysql (we were shipping mysql
on our appliance).
So, before you jump into writing all this code, I would first try to make
your app run in postgress and find out about which mySQL statements are
'extensions' to ANSI standard. Case sensitivity was a huge issue for us, as
in one of those RDBMS was very particular about it. There were some other
issues that I can't remember ATM, but perhaps they've been addressed by
now.
One thing I would maybe suggest is (what I do), write a wrapper around your
wrapper -- AKA Double Bag It. :)
Here at Panasonic Avionics (PAC) we use the PEAR::DB class (the really old
version) since we have to interface with mySQL, SQL Server, Oracle (two
versions). That's where PEAR::DB comes in. However, it's very crude and you
have a lot of redundant code in every page. Like this:
http://pear.php.net/manual/en/package.database.db.intro-fetch.php
You always have to open a connection, test for errors, do the query, test
for errors, fetch the rows, etc..
When I came on board a year ago, I put an end to that micky mouse crap. I
wrote a nice db.inc.php wrapper that handles all that sort of thing, and
then pumps it up like it's on steroids. I added auto-reconnect in case the
connection dropped. I added color-coded SQL output with substitution for
the '?'. I added a last_insert_it() routine which is proprietary to mySQL
BTW (speaking of incompatibilities). I added routines to get an Enum
column, or to get a simple array pairing, etc. It can even force reads from
slave and writes to master! It pretty much kicks ass.
Just simply do this:
$myfoo = sql_query('agis_core', 'SELECT * FROM foo WHERE bar = ?', $bar);
All the minutia is handled for you and $myfoo is now an array of your
results. :)
So, now we code using my wrapper and should we want to switch out the DBAL
later to a more modern one, we just change OUR wrapper calls. There is
minimal overhead, and the pros FAR outweigh any cons.
+1
Though I would use PDO instead of Pear::DB. Also
sequential/autoincrement values are differently specified in
MySQL/PostgreSQL. I did something similar to Daevid using PDO, and also
wrote a last_insert_id() function. It requires the database class to
know what flavor of SQL it's using, and implements the proper function
to return the ID based on that (PostgreSQL has its own version). I would
also suggest that failed queries and commands (not just no useful
result) terminate script execution. PDO functions generally return false
when you feed them absolute garbage, and you don't want to try to
continue execution after that.
An awful lot of SQL is the same between engines, but there are a lot of
edge cases. The only other alternative is something like Active Record,
and I personally wouldn't wish that on anyone. My personal opinion is
that a programmer should learn the SQL dialect he's working with and use
it, rather than something like Active Record. Internally we use
PostgreSQL exclusively. The only time I use MySQL is for customer sites
where their hosting companies don't support PostgreSQL. In that case, I
simply write SQL targetted at MySQL's dialect. It all goes through the
same database class to perform error checking and results return.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
oh, on using adodb.sf.net and 0-overhead for jumping between mysql and
postgresql;
keep all your queries to as simple early-standard sql as possible.
the auto_increment incompatibilities can be circumvented with a
relatively simple
function getMax($table, $field) {
in adodb, you'd loop through a huge dataset like this, ensuring proper
comms mem-usage betweeen the db server and php.
$dbConn = adoEasyConnection(); //returns adodb connection object,
initialized with values from config.php
$sql = 'select * from data where bladiebla=yep';
$q = $dbConn-execute ($sql);
if (!$q || $q-EOF) {
$errorMsg = $dbConn-ErrorMsg();
handleError ($errorMsg, $sql);
} else {
while (!$q-EOF) {
//use $q-fields['field_name']; // from the currently loaded record
$q-MoveNext();
}
}
for short resultsets you could call $q-getRows() to get all the rows
returned as 1 multilevel array.
instead of difficult outer-join constructs and stored procedures,
(that are not portable), i find it much easier to aim for small
intermediate
computation-result arrays in php, which are used to construct
fetch-final-result-sql on the fly.
itnermediate / result arrays can be stored on db / disk in json, too ;)
i built a cms that can store media items and their meta-properties in db,
with the ability to update some meta-properties of an arbitrary
selection of media items to new
values, in 1 go.
i had no problem switching from postgresql to mysql, at all, using the
methods described above.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook g (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:05 +0100, Rene Veerman wrote: i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook g (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk I saw it happen on a site that was getting only about 3000 hits a day. It just takes the right combination of circumstances and it all goes pear shaped. You really should get out of the habit of doing it. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
and after the sleep(rand(1,3)) it might need a short loop like this;
$rnd = rand(1,9); $a=0;
for ($i=0; $i$rnd; $i++) { $a++ }
to further randomize the retry attempt..
On Wed, Feb 3, 2010 at 12:05 AM, Rene Veerman rene7...@gmail.com wrote:
i haven't had the pleasure yet of writing for sites that generate so many
hits/sec that
they'd update the max value of any table at exactly the same time.
i usually ask for the max value about 2 milliseconds before doing the
insert.
And if the insert fails, i can auto-retry via a wrapper function after
sleep(rand(1,3));
I dare say i could work this way at facebook g (not that i really want
to, happy with where i am)
On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.uk
wrote:
On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote:
function getMax($table, $field)
If I saw this sort of code I'd be appalled! It's possibly the worst way to
get the auto increment value. You won't notice it testing the site out on
your own, but all hell will break loose when you start getting a lot of
hits, and two people cause an auto increment at the same time!
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
eh thats randomize the timing of the retry attempt..
On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman rene7...@gmail.com wrote:
and after the sleep(rand(1,3)) it might need a short loop like this;
$rnd = rand(1,9); $a=0;
for ($i=0; $i$rnd; $i++) { $a++ }
to further randomize the retry attempt..
Re: [PHP] database abstraction layer
the auto_increment sytnax is not uniform across servers, is it? On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: I saw it happen on a site that was getting only about 3000 hits a day. It just takes the right combination of circumstances and it all goes pear shaped. You really should get out of the habit of doing it.
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:17 +0100, Rene Veerman wrote:
and after the sleep(rand(1,3)) it might need a short loop like this;
$rnd = rand(1,9); $a=0;
for ($i=0; $i$rnd; $i++) { $a++ }
to further randomize the retry attempt..
On Wed, Feb 3, 2010 at 12:05 AM, Rene Veerman rene7...@gmail.com wrote:
i haven't had the pleasure yet of writing for sites that generate so many
hits/sec that
they'd update the max value of any table at exactly the same time.
i usually ask for the max value about 2 milliseconds before doing the
insert.
And if the insert fails, i can auto-retry via a wrapper function after
sleep(rand(1,3));
I dare say i could work this way at facebook g (not that i really want
to, happy with where i am)
On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.uk
wrote:
On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote:
function getMax($table, $field)
If I saw this sort of code I'd be appalled! It's possibly the worst way to
get the auto increment value. You won't notice it testing the site out on
your own, but all hell will break loose when you start getting a lot of
hits, and two people cause an auto increment at the same time!
Thanks,
Ash
http://www.ashleysheridan.co.uk
The problem is where 2 people choose the same instant to perform an
action on your site that inserts a record into your db. The db engine
inserts them one after the other, and then responds about the max(id) to
your PHP script. Then, you now have 2 people who have the same max(id)
retrieved, but one of the values is wrong.
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:21 +0100, Rene Veerman wrote: the auto_increment sytnax is not uniform across servers, is it? On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: I saw it happen on a site that was getting only about 3000 hits a day. It just takes the right combination of circumstances and it all goes pear shaped. You really should get out of the habit of doing it. It is a MySQL only function. MSSQL has @@IDENTITY, not sure how other engines implement it. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: The problem is where 2 people choose the same instant to perform an action on your site that inserts a record into your db. The db engine inserts them one after the other, and then responds about the max(id) to your PHP script. Then, you now have 2 people who have the same max(id) retrieved, but one of the values is wrong. well, i only use getmaxid()s for inserts. the timelag between getmaxid() and the insert is so small it'd take 300-800 insert-requests/sec (on that particular table) before an error condition would arise. in which case, a tested piece of sql would fail, and can be routed through the retry functions . These would imo provide ample timing re-randomization, aswell as a measure of stress-relief for both php and mysql server. You may correct me if i'm wrong :) BTW: php core developers: can we have a sleep() that accepts a float? :)
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:31 +0100, Rene Veerman wrote: On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: The problem is where 2 people choose the same instant to perform an action on your site that inserts a record into your db. The db engine inserts them one after the other, and then responds about the max(id) to your PHP script. Then, you now have 2 people who have the same max(id) retrieved, but one of the values is wrong. well, i only use getmaxid()s for inserts. the timelag between getmaxid() and the insert is so small it'd take 300-800 insert-requests/sec (on that particular table) before an error condition would arise. in which case, a tested piece of sql would fail, and can be routed through the retry functions . These would imo provide ample timing re-randomization, aswell as a measure of stress-relief for both php and mysql server. You may correct me if i'm wrong :) BTW: php core developers: can we have a sleep() that accepts a float? :) It's the reason transactions exist, to prevent things happening like this. When you have two actions where one is dependent on the other, unless you have a way to tie them together so that they can't be broken, you run the risk of collisions. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
On Wed, Feb 3, 2010 at 12:35 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: It's the reason transactions exist, to prevent things happening like this. When you have two actions where one is dependent on the other, unless you have a way to tie them together so that they can't be broken, you run the risk of collisions. Yea, and i wish they'd standarized features like that across sql servers. But they haven't, so i avoid them like the plague. Whatever dependencies and threading problems might arise, there's always the principle that says: If it doesn't work whlie it should work and threading-timing problems are the only possible cause, then by delay by a random timeperiod and retry the query. In really advanced cases, one can work with last-modified timestamps and/or build up a simple sort of work-queue (also in a table), whereby threads inform each other of the status of their computations.
Re: [PHP] database abstraction layer
Yep, love those race conditions. We have them all over the app cuz the app ciders don't know shit! Bastien Sent from my iPod On Feb 2, 2010, at 5:46 PM, Ashley Sheridan a...@ashleysheridan.co.uk wrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Good lord that is exacty the same logic applied in our app. It only takes about 100 users to create the issue. Bastien Sent from my iPod On Feb 2, 2010, at 6:05 PM, Rene Veerman rene7...@gmail.com wrote: i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook g (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook g (not that i really want to, happy with where i am) This is a race condition... all you need are two hits per day... one from person A and one from person B. If they happen within short enough temporal proximity then the race is on. Now... a big fat lock around the table before the request for max ID and the insert query ought to mitigate your issue. Although I wouldn't call my function getMaxId() I would call it getNextId() :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote:
eh thats randomize the timing of the retry attempt..
On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman rene7...@gmail.com wrote:
and after the sleep(rand(1,3)) it might need a short loop like this;
$rnd = rand(1,9); $a=0;
for ($i=0; $i$rnd; $i++) { $a++ }
to further randomize the retry attempt..
While this decreases the probability of a collision you're just setting
up another race. They teach about this stuff in computer science...
right around first year... or at least they once upon a time did.
Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: The problem is where 2 people choose the same instant to perform an action on your site that inserts a record into your db. The db engine inserts them one after the other, and then responds about the max(id) to your PHP script. Then, you now have 2 people who have the same max(id) retrieved, but one of the values is wrong. well, i only use getmaxid()s for inserts. the timelag between getmaxid() and the insert is so small it'd take 300-800 insert-requests/sec (on that particular table) before an error condition would arise. in which case, a tested piece of sql would fail, and can be routed through the retry functions . These would imo provide ample timing re-randomization, aswell as a measure of stress-relief for both php and mysql server. You may correct me if i'm wrong :) BTW: php core developers: can we have a sleep() that accepts a float? :) This works right up until someone else maintains this system and can't understand why the database is corrupt. Then they find find your database layer and want to stab themselves :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Michael A. Peters wrote:
Robert Cummings wrote:
Rene Veerman wrote:
eh thats randomize the timing of the retry attempt..
On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman rene7...@gmail.com wrote:
and after the sleep(rand(1,3)) it might need a short loop like this;
$rnd = rand(1,9); $a=0;
for ($i=0; $i$rnd; $i++) { $a++ }
to further randomize the retry attempt..
While this decreases the probability of a collision you're just setting
up another race. They teach about this stuff in computer science...
right around first year... or at least they once upon a time did.
Cheers,
Rob.
Einstein I believe said something along the lines of
A smart person solves a problem.
A wise person avoids it in the first place
Might not have been Einstein, but anyway ...
Do you mean the following quote?
The difference between a smart person and a wise person is that
a smart person knows what to say and a wise person knows whether
or not to say it.
Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Robert Cummings wrote: *snip* Einstein I believe said something along the lines of A smart person solves a problem. A wise person avoids it in the first place Might not have been Einstein, but anyway ... Do you mean the following quote? The difference between a smart person and a wise person is that a smart person knows what to say and a wise person knows whether or not to say it. Cheers, Rob. No - this is it: http://www.famousquotes.com/show/1022246/ “A clever person solves a problem. A wise person avoids it.” -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Tue, Feb 02, 2010 at 11:19:29PM +0100, Rene Veerman wrote:
oh, on using adodb.sf.net and 0-overhead for jumping between mysql and
postgresql;
keep all your queries to as simple early-standard sql as possible.
the auto_increment incompatibilities can be circumvented with a
relatively simple
function getMax($table, $field) {
This approach is guaranteed to run into race conditions. The only way to
positively ensure proper results is to let the DB engine take care of it
itself. The engines typically track incremental IDs by session, which
prevents you from getting an ID someone else has just used.
in adodb, you'd loop through a huge dataset like this, ensuring proper
comms mem-usage betweeen the db server and php.
$dbConn = adoEasyConnection(); //returns adodb connection object,
initialized with values from config.php
$sql = 'select * from data where bladiebla=yep';
$q = $dbConn-execute ($sql);
if (!$q || $q-EOF) {
$errorMsg = $dbConn-ErrorMsg();
handleError ($errorMsg, $sql);
} else {
while (!$q-EOF) {
//use $q-fields['field_name']; // from the currently loaded record
$q-MoveNext();
}
}
for short resultsets you could call $q-getRows() to get all the rows
returned as 1 multilevel array.
instead of difficult outer-join constructs and stored procedures,
(that are not portable), i find it much easier to aim for small
intermediate
computation-result arrays in php, which are used to construct
fetch-final-result-sql on the fly.
itnermediate / result arrays can be stored on db / disk in json, too ;)
For MySQL I would agree. But I prefer the ability to use the full SQL
standard when manipulating a database; that is, all joins, foreign keys,
etc. For that same reason, I tend to avoid stored procedures as well. If
I have to do things like handle foreign key constraints in my PHP code
(instead of letting the DBMS handle them), I have to wonder why I'm even
using a relational DBMS.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lol, damn iPod corrections. The app designers is what was meant. Bastien Sent from my iPod On Feb 2, 2010, at 8:41 PM, Robert Cummings rob...@interjinn.com wrote: Phpster wrote: Yep, love those race conditions. We have them all over the app cuz the app ciders don't know shit! Mmmm... apple cider... to cure what ails you or at least get you drunk enough to not care about the horrible race conditions :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Op 2/3/10 12:19 AM, Ashley Sheridan schreef: On Wed, 2010-02-03 at 00:21 +0100, Rene Veerman wrote: the auto_increment sytnax is not uniform across servers, is it? On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: I saw it happen on a site that was getting only about 3000 hits a day. It just takes the right combination of circumstances and it all goes pear shaped. You really should get out of the habit of doing it. It is a MySQL only function. MSSQL has @@IDENTITY, not sure how other engines implement it. firebird does it via what they call 'generators', 2 seconds of searching shows postgres has this: CREATE TABLE tableName ( id serial PRIMARY KEY, name varchar(50) UNIQUE NOT NULL, dateCreated timestamp DEFAULT current_timestamp ); you can bet you ass that every other DB out there that's worth it's salt has atomic id incrementor functionality exposed in some way or other. @Rene: all that talk of maxId functions and random retries etc, etc, is complete pooh. don't do it, **please** use the proper tools provided by the DB in question. Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Wed, Feb 3, 2010 at 5:49 AM, Jochem Maas joc...@iamjochem.com wrote: you can bet you ass that every other DB out there that's worth it's salt has atomic id incrementor functionality exposed in some way or other. @Rene: all that talk of maxId functions and random retries etc, etc, is complete pooh. don't do it, **please** use the proper tools provided by the DB in question. i just checked how my 1 app that did generate over a million hits/day (all with an insert for stats purposes) for a few weeks handles the getMax issue, and i see i did use mysql's auto_increment there. i suppose the difference in syntax between sql servers for this one is acceptable. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Wed, Feb 03, 2010 at 06:39:29AM +0100, Rene Veerman wrote: On Wed, Feb 3, 2010 at 5:49 AM, Jochem Maas joc...@iamjochem.com wrote: you can bet you ass that every other DB out there that's worth it's salt has atomic id incrementor functionality exposed in some way or other. @Rene: all that talk of maxId functions and random retries etc, etc, is complete pooh. don't do it, **please** use the proper tools provided by the DB in question. i just checked how my 1 app that did generate over a million hits/day (all with an insert for stats purposes) for a few weeks handles the getMax issue, and i see i did use mysql's auto_increment there. i suppose the difference in syntax between sql servers for this one is acceptable. Am I the only one who's seeing Rene's replies but not the posts which generated them? Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
