[PHP-DB] EZPDO Replacement
One of our programmers had done a fair amount of coding using the EZPDO ORM library and it is no longer supported, Does anyone have any experience migrating from EZPDO to another ORM library. Any recommendations?, Thanks in advance for your help. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Random pick
Hi everyone, I've been lurking in the shadows on the list for quite some time and now I'm in need of a little info or advise. I'm looking for a way to grab a record out of a database on a certain day each month and I'm wondering if this can be accomplished with just a mysql query or would I need to use PHP also? My hunch is mixing the two, but I'm not very prolific with mysql at this point so I thought It would be better to ask before I go and get myself all mixed up. Any suggestions? -- Blessings David M. I have been driven to my knees many times by the overwhelming conviction that I had nowhere else to go. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Random pick
David McGlone wrote: Hi everyone, I've been lurking in the shadows on the list for quite some time and now I'm in need of a little info or advise. I'm looking for a way to grab a record out of a database on a certain day each month and I'm wondering if this can be accomplished with just a mysql query or would I need to use PHP also? A mysql query would do it but you'd use something to talk to mysql and process the results (whether it's php, perl, python, ruby etc depends on your other requirements). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Hello
Karl DeSaulniers wrote:
HI,
Thanks for your response. Here is my query. UserID is auto incrament and
UserLastLogin is a current_timestamp.
$query_users = INSERT INTO users(UserID, Username, UserEmail,
UserPassword, UserFirstName, UserLastName, UserCompany, UserAddress,
UserAddress2, UserCity, UserState, UserCountry, UserZip, UserPhone,
UserFax, UserEmailVerified, UserRegistrationDate, UserVerificationCode,
UserIP, UserLastLogin)
VALUES('NULL','.$Username.','.$UserEmail.','.$UserPassword.','.$UserFirstName.','.$UserLastName.','.$UserCompany.','.$UserAddress.','.$UserAddress2.','.$UserCity.','.$UserState.','.$UserCountry.','.$UserZip.','.$UserPhone.','.$UserFax.','.$UserEmailVerified.','.$UserRegistrationDate.','.$UserVerificationCode.','.$UserIP.',
now());
This works as far as populating the database, but my results page does
not return anything.
Only if the VALUES is set like this:
VALUES('NULL','.$Username=$_POST['Username'].','.$UserEmail=$_POST['UserEmail'].','.$UserPassword=$_POST['UserPassword'].','.$UserFirstName=$_POST['UserFirstName'].','.$UserLastName=$_POST['UserLastName'].','.$UserCompany=$_POST[$UserCompany].','.$UserAddress=$_POST['UserAddress'].','.$UserAddress2=$_POST['UserAddress2'].','.$UserCity=$_POST['UserCity'].','.$UserState=$_POST['UserState'].','.$UserCountry=$_POST[$UserCountry].','.$UserZip=$_POST['UserZip'].','.$UserPhone=$_POST['UserPhone'].','.$UserFax=$_POST[$UserFax].','.$UserEmailVerified=$_POST[$UserEmailVerified].','.$UserRegistrationDate=$_POST[$UserRegistrationDate].','.$UserVerificationCode=$_POST['UserVerificationCode'].','.$UserIP=$_POST[$UserIP].',
now());
but some do not work with this setup. variables like $UserEmailVerified,
$UserRegistrationDate and $UserIP are not created from the form that was
submitted.
for example, User IP date is created like this.
$UserIP = md5($_SERVER[REMOTE_ADDR]);
Problem 1 is sql injection. Wrap each variable in a
mysql_real_escape_string call:
insert into table (...) values (' . mysql_real_escape_string($username)
. '
also quoting 'NULL' means it will add 'NULL' as the id - not what you
want. You can leave out the column to use the default from the database.
Any errors from mysql?
Add:
echo mysql_error();
after your insert call.
-
Below is a snip of how I retrieve the info on the result page (dont want
to clutter with whole code. also $fieldOne etc are MySql wildcards '%'
from some dropdown lists that show before this code is executed. The
results from adding show up fine there.)
$query_users = SELECT * FROM users WHERE UserID LIKE '$fieldOne' AND
Username LIKE '$fieldTwo' AND UserEmail LIKE '$fieldThree' AND
UserPassword LIKE '$fieldFour' AND UserFirstName LIKE '$fieldFive' AND
UserLastName LIKE '$fieldSix' AND UserCompany LIKE '$fieldSeven' AND
UserAddress LIKE '$fieldEight' AND UserAddress2 LIKE '$fieldNine' AND
UserCity LIKE '$fieldTen' AND UserState LIKE '$fieldEleven' AND
UserCountry LIKE '$fieldTwelve' AND UserZip LIKE '$fieldThirteen' AND
UserPhone LIKE '$fieldFourteen' AND UserFax LIKE '$fieldFifteen' AND
UserEmailVerified LIKE '$fieldSixteen' AND UserRegistrationDate LIKE
'$fieldSeventeen' AND UserVerificationCode LIKE '$fieldEighteen' AND
UserIP LIKE '$fieldNineteen' AND UserLastLogin LIKE '$fieldTwenty' LIMIT
$min, $max_results;
Again you need to escape all your data (except $min, $max_results - just
make sure they are always integers).
I'm assuming there are no errors reported by mysql.
To debug this, I'd simplify the query and work out which bit isn't
matching what you want (it could be $fieldOne isn't quite what you
expect, or it could be $fieldEleven or $fieldEighteen or ..).
Start off with one field, then add another and go from there.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Random pick
On Monday 14 December 2009 21:02:37 Chris wrote:
David McGlone wrote:
Hi everyone,
I've been lurking in the shadows on the list for quite some time and now
I'm in need of a little info or advise.
I'm looking for a way to grab a record out of a database on a certain day
each month and I'm wondering if this can be accomplished with just a
mysql query or would I need to use PHP also?
A mysql query would do it but you'd use something to talk to mysql and
process the results (whether it's php, perl, python, ruby etc depends on
your other requirements).
What I'm trying to do is to have a record picked from the database at random
on the 1st of every month and display it on the page. Here is some code I have
been working with:
$query = SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0);
$result = @mysql_query($query);
if (!$result){
$query = SELECT * FROM monthlyPooch ORDER BY RAND() LIMIT 1;
$result = @mysql_query($query);
while ($row = mysql_fetch_array($result)){
echo $row[poochName] ;
echo $row[Birthdate];
}
}
I suspect this code isn't going to work, because I think on the 1st day of the
month it's going to choke.
What do you think?
--
Blessings
David M.
I have been driven to my knees many times by the overwhelming conviction that
I had nowhere else to go.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Hello
Hi Chris,
On Dec 14, 2009, at 8:09 PM, Chris wrote:
Problem 1 is sql injection. Wrap each variable in a
mysql_real_escape_string call:
insert into table (...) values (' . mysql_real_escape_string
($username) . '
At one point I did have the mysql_real_escape_string() and it worked
the same as without as far as populating the database.
But when I would view results, it didnt read anything from the database.
also quoting 'NULL' means it will add 'NULL' as the id - not what
you want. You can leave out the column to use the default from the
database.
Actually it works fine with 'NULL' for some reason. UserID is an auto
Incrament and if I take $UserID out as well as its VALUE, I get an
error for number of fields not matching.
Any errors from mysql?
Add:
echo mysql_error();
after your insert call.
Again you need to escape all your data (except $min, $max_results -
just make sure they are always integers).
Those are so I can control the number of items shown per page.
I'm assuming there are no errors reported by mysql.
To debug this, I'd simplify the query and work out which bit isn't
matching what you want (it could be $fieldOne isn't quite what you
expect, or it could be $fieldEleven or $fieldEighteen or ..).
Start off with one field, then add another and go from there.
Basically the result page would not show anything in the database
unless it was inserted in the database using the $_POST method.
Not sure why, but I have since redone the result page utilizing a
different method of retrieval and it looks to be working.
Thank you all for your responses. very quick I might add. :)
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Re: [PHP-DB] Hello
What does this do exactly? Documentation was a bit fuzzy for me. Is it needed at all times to protect with? On Dec 14, 2009, at 8:22 PM, Karl DeSaulniers wrote: mysql_real_escape_string() Karl DeSaulniers Design Drumm http://designdrumm.com
Re: [PHP-DB] Mysql query
ron.pigg...@actsministries.org wrote:
The query from my previous post was only part of a larger query. This is
the entire query:
SELECT GREATEST( IF( CURDATE( ) = DATE_SUB( DATE( FROM_UNIXTIME(
1239508800 ) ) , INTERVAL LEAST( 14, (
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =1 ) )
DAY )
AND CURDATE( ) = DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF(
CURDATE( ) = DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2 ) )
DAY )
AND CURDATE( ) = DATE( 2009 -12 -25 ) , 2, 0
)
) AS verse_application
It took me a while to work out what this was trying to do, that's
complicated.
Reformatted a little:
SELECT
GREATEST(
IF
(
CURDATE() =
DATE_SUB(
DATE(FROM_UNIXTIME(1239508800)),
INTERVAL LEAST(14, (SELECT 1)) DAY)
AND CURDATE() = DATE(FROM_UNIXTIME(1239508800)),
1,
0
),
IF
(
CURDATE() =
DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT 2)) DAY)
AND CURDATE() = DATE('2009-12-25'),
2,
0
)
) AS verse_application;
(which isn't much better in email).
You're not getting '2' because the second part is returning 0.
I substituted dummy variables for your subqueries (select 1 and select 2).
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2;
What does that return by itself?
that is what your query will run instead of my 'select 2'.
That in turn goes into the
select least(14, result_from_above_query);
and takes that away from date('2009-12-25');
If the current date is not in that range, it will return 0.
Here's the second part of your query isolated for you to test:
SELECT
IF
(
CURDATE() =
DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT COUNT(*) FROM
verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY)
AND CURDATE() = DATE('2009-12-25'),
2,
0
)
;
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Hello
Karl DeSaulniers wrote:
Hi Chris,
On Dec 14, 2009, at 8:09 PM, Chris wrote:
Problem 1 is sql injection. Wrap each variable in a
mysql_real_escape_string call:
insert into table (...) values (' .
mysql_real_escape_string($username) . '
At one point I did have the mysql_real_escape_string() and it worked the
same as without as far as populating the database.
Did you try names with single quotes? (Tim O'Reilly is a common example
to try).
But when I would view results, it didnt read anything from the database.
Sure it went in? Did you see the data when you viewed the table in
phpmyadmin or some other tool?
Again you need to escape all your data (except $min, $max_results -
just make sure they are always integers).
Those are so I can control the number of items shown per page.
I realise that. mysql_real_escape_string is used for data in your query,
and may cause problems if used in limit clauses. If you end up with this
for example:
select * from table limit mysql_real_escape_string('blah');
of course it's not going work.
Hence the check to make sure $min and $max_results are int's before
passing them to the query so if anyone messes with them it won't break
your queries.
if (!is_int($min)) {
$min = 0;
}
if (!is_int($max_results)) {
$max_results = 5;
}
Basically the result page would not show anything in the database unless
it was inserted in the database using the $_POST method.
That still suggests an error with the insert.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Random pick
David McGlone wrote:
On Monday 14 December 2009 21:02:37 Chris wrote:
David McGlone wrote:
Hi everyone,
I've been lurking in the shadows on the list for quite some time and now
I'm in need of a little info or advise.
I'm looking for a way to grab a record out of a database on a certain day
each month and I'm wondering if this can be accomplished with just a
mysql query or would I need to use PHP also?
A mysql query would do it but you'd use something to talk to mysql and
process the results (whether it's php, perl, python, ruby etc depends on
your other requirements).
What I'm trying to do is to have a record picked from the database at random
on the 1st of every month and display it on the page. Here is some code I have
been working with:
$query = SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0);
$result = @mysql_query($query);
if (!$result){
$query = SELECT * FROM monthlyPooch ORDER BY RAND() LIMIT 1;
$result = @mysql_query($query);
while ($row = mysql_fetch_array($result)){
echo $row[poochName] ;
echo $row[Birthdate];
}
}
You can check the day of the month in php then do your other query:
$today = date('j');
if ($today !== 1) {
echo Today isn't the first. No need to continue.\n;
exit;
}
..
random query etc here.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Random pick
Chris wrote:
David McGlone wrote:
On Monday 14 December 2009 21:02:37 Chris wrote:
David McGlone wrote:
Hi everyone,
I've been lurking in the shadows on the list for quite some time and
now
I'm in need of a little info or advise.
I'm looking for a way to grab a record out of a database on a
certain day
each month and I'm wondering if this can be accomplished with just a
mysql query or would I need to use PHP also?
A mysql query would do it but you'd use something to talk to mysql and
process the results (whether it's php, perl, python, ruby etc depends on
your other requirements).
What I'm trying to do is to have a record picked from the database at
random on the 1st of every month and display it on the page. Here is
some code I have been working with:
$query = SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0);
$result = @mysql_query($query);
if (!$result){
$query = SELECT * FROM monthlyPooch ORDER BY RAND() LIMIT 1;
$result = @mysql_query($query);
while ($row = mysql_fetch_array($result)){
echo $row[poochName] ;
echo $row[Birthdate];
}
}
You can check the day of the month in php then do your other query:
$today = date('j');
See http://php.net/date for date formats and what they return.
if ($today !== 1) {
echo Today isn't the first. No need to continue.\n;
exit;
}
..
random query etc here.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql query
Chris I spent 3 hours debugging this query myself. I got as far as
putting '' around 2009-12-25 to get the desired results. I just added the
word DATE. It works, thanks.
Chris I run a verse of the day e-mail list. This query determines the
logic of the content (year round, Easter and Christmas). It is quite the
query to say the least.
Thanks for your help.
Sincerely,
Ron
www.TheVerseOfTheDay.info
ron.pigg...@actsministries.org wrote:
The query from my previous post was only part of a larger query. This
is
the entire query:
SELECT GREATEST( IF( CURDATE( ) = DATE_SUB( DATE( FROM_UNIXTIME(
1239508800 ) ) , INTERVAL LEAST( 14, (
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =1 ) )
DAY )
AND CURDATE( ) = DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF(
CURDATE( ) = DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2 ) )
DAY )
AND CURDATE( ) = DATE( 2009 -12 -25 ) , 2, 0
)
) AS verse_application
It took me a while to work out what this was trying to do, that's
complicated.
Reformatted a little:
SELECT
GREATEST(
IF
(
CURDATE() =
DATE_SUB(
DATE(FROM_UNIXTIME(1239508800)),
INTERVAL LEAST(14, (SELECT 1)) DAY)
AND CURDATE() = DATE(FROM_UNIXTIME(1239508800)),
1,
0
),
IF
(
CURDATE() =
DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT 2)) DAY)
AND CURDATE() = DATE('2009-12-25'),
2,
0
)
) AS verse_application;
(which isn't much better in email).
You're not getting '2' because the second part is returning 0.
I substituted dummy variables for your subqueries (select 1 and select 2).
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2;
What does that return by itself?
that is what your query will run instead of my 'select 2'.
That in turn goes into the
select least(14, result_from_above_query);
and takes that away from date('2009-12-25');
If the current date is not in that range, it will return 0.
Here's the second part of your query isolated for you to test:
SELECT
IF
(
CURDATE() =
DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT COUNT(*) FROM
verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY)
AND CURDATE() = DATE('2009-12-25'),
2,
0
)
;
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
