Re: [PHP] MySQL -- finding whether there's a transaction started
On 24-Apr-09 03:45, Chris wrote: I don't think mysql has any way of finding that out. If you're using an abstraction layer, it's easy enough in code - though rollback's are a little harder - should they do a complete rollback or just to a savepoint? Thank you for taking the time to sketch that mock-up -- yes, we were also thinking about something similar as a last resort, but I just can't believe you can't simply ask MySQL whether it's going to autocommit the next query or not... Bogdan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL, MD5 and SHA1
On Wed, Apr 22, 2009 at 10:25 AM, Jan G.B. ro0ot.w...@googlemail.com wrote: 2009/4/21 Per Jessen p...@computer.org: Jan G.B. wrote: A web application that uses an external db server would be quite ... uhm... slow! Anyone did this, yet? ;) Certainly, and it's not slow. It depends entirely on your connection to the public internet. As we're speaking of the internet, it also depends on the route and so it depends on servers which are not underlying your administration (in most cases at least). Having several servers with gigabit internet access also might be more expensive than a cat6 patch cable and a gigabit nic. So this setup would be just mad. Regards There are definitely other ways to make the communication secure, whether you route it through an local subnet, configure a totally private network using secondary NICs, encrypt the connection, host both on the same machine and use a local loopback address, etc. If you've done one or more of these things to secure the pathway between the web server and the database, and if you know that no one is going to come behind you and reconfigure things, then you can probably rest fairly comfortably passing unhashed/unencrypted values to MySQL all you want. I only made the suggestion as a matter of defensive programming, since not everyone developing with PHP knows and/or has control over how PHP and MySQL communicate. For many sites, a developer writing an app has no idea whether the web server and database are on the same physical machine, let alone how the two services communicate. What's more, in some of these environments, what is true today could change tomorrow. (The issue of query logs is another good point, too.) All I'm saying is if you hash/encrypt the value in PHP rather than passing it off to MySQL in open text, you don't have to worry about whether the connection is (or becomes) sniffable. For that matter if you're going to bring up performance, I know many DBAs who would argue this as performance issue as well. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL -- finding whether there's a transaction started
Bogdan Stancescu wrote:
Hello list,
I'm developing a library and would need to know if the code calling my
library has already started a MySQL transaction or not. I want to know
whether I should start one or use savepoints instead -- starting a
transaction if one is already in progress commits the existing
transaction, and setting a savepoint silently fails outside
transactions. And I was unable to find any non-destructive way of
retrieving that information -- is there any?
I don't think mysql has any way of finding that out. If you're using an
abstraction layer, it's easy enough in code - though rollback's are a
little harder - should they do a complete rollback or just to a savepoint?
class db
{
private $transaction_count = 0;
public function startTransaction()
{
if ($this-transaction_count == 0) {
mysql_query(BEGIN);
} else {
mysql_query(SAVEPOINT);
}
$this-transaction_count++;
}
public function commitTransaction()
{
// you can't commit a transaction if there's more than one open
// so just decrement the counter
if ($this-transaction_count 1) {
$this-transaction_count--;
return;
}
$this-transaction_count = 0;
mysql_query(COMMIT);
}
}
Now you can just call
$db-startTransaction();
and
$db-commitTransaction();
and it'll handle the stuff for you.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL, MD5 and SHA1
2009/4/21 Per Jessen p...@computer.org: Jan G.B. wrote: A web application that uses an external db server would be quite ... uhm... slow! Anyone did this, yet? ;) Certainly, and it's not slow. It depends entirely on your connection to the public internet. As we're speaking of the internet, it also depends on the route and so it depends on servers which are not underlying your administration (in most cases at least). Having several servers with gigabit internet access also might be more expensive than a cat6 patch cable and a gigabit nic. So this setup would be just mad. Regards -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL, MD5 and SHA1
Grega Leskovsek wrote: provided I want to store hash of a password in MySQL ... Using MySQL, the whole check can be achieved with a SQL query, since the MD5 function is provided as part of the database query language ... Can I use also SHA1 or must I use MD5? You could have just checked the mysql manual? But yes, there is also a SHA1 function. /Per -- Per Jessen, Zürich (19.2°C) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL, MD5 and SHA1
On Tue, Apr 21, 2009 at 8:34 AM, Grega Leskovsek mavri...@gmail.com wrote: provided I want to store hash of a password in MySQL ... Using MySQL, the whole check can be achieved with a SQL query, since the MD5 function is provided as part of the database query language ... Can I use also SHA1 or must I use MD5? Thanks in advance, -- When the sun rises I receive and when it sets I forgive - http://users.skavt.net/~gleskovs/ All the Love, Grega Leskov'sek I would encode the value in PHP and pass the hash to MySQL rather than passing the password in open text as part of the query and letting MySQL calculate the hash. That way the sensitive data has already been hashed and you don't have to worry about whether the communication between PHP and MySQL travels over an unencrypted network connection -- now or in the future. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL, MD5 and SHA1
2009/4/21 Andrew Ballard aball...@gmail.com: On Tue, Apr 21, 2009 at 8:34 AM, Grega Leskovsek mavri...@gmail.com wrote: provided I want to store hash of a password in MySQL ... Using MySQL, the whole check can be achieved with a SQL query, since the MD5 function is provided as part of the database query language ... Can I use also SHA1 or must I use MD5? Thanks in advance, -- When the sun rises I receive and when it sets I forgive - http://users.skavt.net/~gleskovs/ All the Love, Grega Leskov'sek I would encode the value in PHP and pass the hash to MySQL rather than passing the password in open text as part of the query and letting MySQL calculate the hash. That way the sensitive data has already been hashed and you don't have to worry about whether the communication between PHP and MySQL travels over an unencrypted network connection -- now or in the future. Well, It's a point. But regarding that in most setups the database server is only accessible in a local subnet or is only listening on the loopback device of the same server hosting the webserver, you must not think about someone sniffing your traffic. A web application that uses an external db server would be quite ... uhm... slow! Anyone did this, yet? ;) I'm using md5()/sha1() in several stored procedures and I feel quite safe with it. Regards Andrew -- 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] MySQL, MD5 and SHA1
Jan G.B. wrote: A web application that uses an external db server would be quite ... uhm... slow! Anyone did this, yet? ;) Certainly, and it's not slow. It depends entirely on your connection to the public internet. -- Per Jessen, Zürich (18.2°C) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL, MD5 and SHA1
Andrew Ballard wrote: On Tue, Apr 21, 2009 at 8:34 AM, Grega Leskovsek mavri...@gmail.com wrote: provided I want to store hash of a password in MySQL ... Using MySQL, the whole check can be achieved with a SQL query, since the MD5 function is provided as part of the database query language ... Can I use also SHA1 or must I use MD5? Thanks in advance, -- When the sun rises I receive and when it sets I forgive - http://users.skavt.net/~gleskovs/ All the Love, Grega Leskov'sek I would encode the value in PHP and pass the hash to MySQL rather than passing the password in open text as part of the query and letting MySQL calculate the hash. That way the sensitive data has already been hashed and you don't have to worry about whether the communication between PHP and MySQL travels over an unencrypted network connection -- now or in the future. Andrew In addition, you don't want the password showing up in a general query log for the server. -- Micah -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MYSQL TABLES (One To Many Relationship)
Thanks Tedd, You're such an angel. I've tried it your way and it works perfectly well. (maybe I'll send you a return ticket to Nigeria when I organize the PHP conference). Hey Dan, help me amplify my thanks to Tedd. Alugo Abdulazeez www.frangeovic.com Date: Sun, 22 Mar 2009 14:38:01 -0400 To: defati...@hotmail.com; php-general@lists.php.net From: tedd.sperl...@gmail.com Subject: Re: [PHP] MYSQL TABLES (One To Many Relationship) At 5:04 PM +0100 3/22/09, abdulazeez alugo wrote: Hi guys, I need help on something I'm working on and its really eating me up so I'll appreciate any help I can get on it. I'm writing code for a site that posts a topic for discussion and accepts comments (Just like a parliament). There are different discussions for every day and the comments should be displayed along with the corresponding topics per day. Hi Alugo: I wrote a similar thing for my site, see here: http://sperling.com As you can see, on most pages people can add comment. Considering such, the most important part I found was designing the tables for the database. I used two tables, which follow showing fields: COMMENTS table Fields id -- auto-increment id for comments post_time -- time of this post poster_id -- the id from the POSTERS table page -- the page the poster commented on comment -- the actual comment made by the poster notify_me -- an option for the poster to select IF they want to be notified of additional posts approved -- option for me to use if I want the post to be displayed POSTERS table Fields id -- auto-increment id for posters time -- time of first post (i.e., registration) poster -- name of poster email -- email of poster (after confirmation of email address) ip -- ip of poster (taken from post) web_site -- web site of poster (if given) banned -- option for me to use if I want to ban this poster So as you can see, this is one table for posters and each poster can post many comments (i.e., a one to many relationship). Each time a post is made, the posting method checks the database for poster approval. Either the poster is approved, banned, or yet to be registered -- each path is followed accordingly. Note as each page is loaded the comments are pulled from the database and shown, or not, depending upon if the poster is approved OR if I have overridden the post. This allows me to ban the post, but not the poster. But in most cases, if the poster post something that I don't like, then the poster is also banned. HTH's tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
Re: [PHP] MYSQL TABLES (One To Many Relationship)
At 5:04 PM +0100 3/22/09, abdulazeez alugo wrote: Hi guys, I need help on something I'm working on and its really eating me up so I'll appreciate any help I can get on it. I'm writing code for a site that posts a topic for discussion and accepts comments (Just like a parliament). There are different discussions for every day and the comments should be displayed along with the corresponding topics per day. Hi Alugo: I wrote a similar thing for my site, see here: http://sperling.com As you can see, on most pages people can add comment. Considering such, the most important part I found was designing the tables for the database. I used two tables, which follow showing fields: COMMENTS table Fields id -- auto-increment id for comments post_time -- time of this post poster_id -- the id from the POSTERS table page -- the page the poster commented on comment -- the actual comment made by the poster notify_me -- an option for the poster to select IF they want to be notified of additional posts approved -- option for me to use if I want the post to be displayed POSTERS table Fields id -- auto-increment id for posters time -- time of first post (i.e., registration) poster -- name of poster email -- email of poster (after confirmation of email address) ip -- ip of poster (taken from post) web_site -- web site of poster (if given) banned -- option for me to use if I want to ban this poster So as you can see, this is one table for posters and each poster can post many comments (i.e., a one to many relationship). Each time a post is made, the posting method checks the database for poster approval. Either the poster is approved, banned, or yet to be registered -- each path is followed accordingly. Note as each page is loaded the comments are pulled from the database and shown, or not, depending upon if the poster is approved OR if I have overridden the post. This allows me to ban the post, but not the poster. But in most cases, if the poster post something that I don't like, then the poster is also banned. HTH's tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Chris wrote: That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. I agree with chris and I do the same thing, also with a class or a wrapper function/s that handle your queries you can also decided on what databases the query should run, say you have Master-Slave replication setup with mysql, you could run selects on the slaves and updates/delete in the master without the class deciding which one it should run on Clive -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
2009/1/21 Jay Moore jaymo...@accu-com.com This is a MySQL class I use and I wanted to get everyone's thoughts on how/if I can improve it. This is for MySQL only. I don't need to make it compatible with other databases. I'm curious what you all think. Thanks, Jay Hey, 1. You know the mysqli-Class? 2. If yes, than I don't get it in which way this will improve mysql handling -eddy
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 9:45 AM, Edmund Hertle edmund.her...@student.kit.edu wrote: 2009/1/21 Jay Moore jaymo...@accu-com.com This is a MySQL class I use and I wanted to get everyone's thoughts on how/if I can improve it. This is for MySQL only. I don't need to make it compatible with other databases. I'm curious what you all think. Thanks, Jay Hey, 1. You know the mysqli-Class? 2. If yes, than I don't get it in which way this will improve mysql handling -eddy MySQLi actually stands for MySQL Improved. Problem solved. -- Kyle Terry | www.kyleterry.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 12:45 PM, Edmund Hertle edmund.her...@student.kit.edu wrote: 2009/1/21 Jay Moore jaymo...@accu-com.com This is a MySQL class I use and I wanted to get everyone's thoughts on how/if I can improve it. This is for MySQL only. I don't need to make it compatible with other databases. I'm curious what you all think. Thanks, Jay Hey, 1. You know the mysqli-Class? 2. If yes, than I don't get it in which way this will improve mysql handling -eddy Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. mysql_real_escape_string should use $this-link to properly escape based on charset, not server default. I'd also call it escape. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 11:37:07AM -0600, Jay Moore wrote:
This is a MySQL class I use and I wanted to get everyone's thoughts on
how/if I can improve it. This is for MySQL only. I don't need to make
it compatible with other databases. I'm curious what you all think.
Thanks,
Jay
Class:
--
?php
// Standard MySQL class
class do_mysql
{
// Constructor
function __construct()
{
$this-do_mysql();
}
// Destructor
function __destruct()
{
//$this-close();
}
function do_mysql()
{
$this-login = '';
$this-pass = '';
$this-link = @mysql_connect('localhost', $this-login,
$this-pass)
or die('Could not connect to the database.');
} // End do_mysql
// Functions
function close()
{
if ($this-link)
{
mysql_close($this-link);
unset($this-link);
}
} // End close
function fetch_array()
{
return mysql_fetch_array($this-result);
} // End fetch_array
function last_id()
{
return mysql_insert_id($this-link);
} // End last_id
function num_rows()
{
return mysql_num_rows($this-result);
} // End num_rows
function process($database = '')
{
if (is_null($this-query))
{
die('Error: Query string empty. Cannot proceed.');
}
$this-db = @mysql_select_db($database, $this-link)
or die(Database
Error:Couldn't select $database br / . mysql_error());
$this-result = @mysql_query($this-query, $this-link) or
die('Database Error: Couldn\'t query. br /' . mysql_error() . br
/br / $this-query);
} // End process
function sanitize($ref)
{
$ref = mysql_real_escape_string($ref);
} // End sanitize
} // End do_mysql
?
Sample usage:
$value = 'value';
$sql = new do_mysql();
$sql-sanitize($value);
$sql-query = SELECT * FROM `wherever` WHERE `field` = '$value';
$sql-process('dbname');
$sql-close();
if ($sql-num_rows())
{
while ($row = $sql-fetch_array())
{
do stuff;
}
}
A couple of thoughts. First precede all your mysql_* calls with the at
sign (@) to shut up the routines if they generate text. I had this
problem, and that was the answer.
Second, store your connection resource as a class variable, so you can
pass it around to the various routines. Actually, you're already doing
this, but I prefer to do so explicitly, as:
var $link;
at the top of the class.
I have a similar class for PostgreSQL. I also have routines like
update, which allow you to pass a table name and an associative array
of field values. Same thing for an insert routine.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
This is a MySQL class I use and I wanted to get everyone's thoughts on how/if I can improve it. This is for MySQL only. I don't need to make it compatible with other databases. I'm curious what you all think. I have a similar thing I use, which uses the same (or at least very similar) API to the PEAR::DB abstraction layer. http://www.phpguru.org/downloads/DB/ -- Richard Heyes HTML5 Graphing for Firefox, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 17th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Paul M Foster schreef:
On Wed, Jan 21, 2009 at 11:37:07AM -0600, Jay Moore wrote:
This is a MySQL class I use and I wanted to get everyone's thoughts on
how/if I can improve it. This is for MySQL only. I don't need to make
it compatible with other databases. I'm curious what you all think.
I'd try to move to using the mysqli functions or class, which
you can still wrap in a custom object (I do this because
I like to minimize the interface to the bare, bare minimum ...
professional laziness you might say).
does you class need to be php4 compatible? I'd hope
not but you may still have to support php4 ... even then
I'd doubt you'd be using php4 for new project so it
might be worth making a new php5 only class.
...
Thanks,
Jay
Class:
--
?php
// Standard MySQL class
class do_mysql
{
// Constructor
function __construct()
{
$this-do_mysql();
}
// Destructor
function __destruct()
{
//$this-close();
}
function do_mysql()
{
$this-login = '';
$this-pass = '';
$this-link = @mysql_connect('localhost', $this-login,
$this-pass)
or die('Could not connect to the database.');
} // End do_mysql
// Functions
function close()
{
if ($this-link)
{
mysql_close($this-link);
unset($this-link);
}
} // End close
function fetch_array()
{
return mysql_fetch_array($this-result);
} // End fetch_array
function last_id()
{
return mysql_insert_id($this-link);
} // End last_id
function num_rows()
{
return mysql_num_rows($this-result);
} // End num_rows
function process($database = '')
{
if (is_null($this-query))
{
die('Error: Query string empty. Cannot proceed.');
}
$this-db = @mysql_select_db($database, $this-link)
or die(Database
Error: Couldn't select $database br / . mysql_error());
$this-result = @mysql_query($this-query, $this-link) or
die('Database Error: Couldn\'t query. br /' . mysql_error() . br
/br / $this-query);
} // End process
function sanitize($ref)
{
$ref = mysql_real_escape_string($ref);
} // End sanitize
} // End do_mysql
?
Sample usage:
$value = 'value';
$sql = new do_mysql();
$sql-sanitize($value);
$sql-query = SELECT * FROM `wherever` WHERE `field` = '$value';
$sql-process('dbname');
$sql-close();
if ($sql-num_rows())
{
while ($row = $sql-fetch_array())
{
do stuff;
}
}
here's another (php5) version of your class,
see what you think:
?php
class do_mysql
{
private $link;
private $result;
private $db;
// Constructor
function __construct($login, $pass, $db, $server = 'localhost')
{
$this-link = mysql_connect($server, $login, $pass);
if (!$this-link)
throw new Exception('Could not connect to the database.');
$this-setDB($db);
}
// Destructor
function __destruct()
{
$this-close();
}
// Functions
function close()
{
if (!$this-link)
return;
mysql_close($this-link);
unset($this-link);
}
function fetch_array()
{
return mysql_fetch_array($this-result);
}
function last_id()
{
return mysql_insert_id($this-link);
}
function num_rows()
{
return mysql_num_rows($this-result);
}
function setDB($database)
{
if (!$database || !mysql_select_db($database, $this-link))
throw new Exception(Database Error: Couldn't select $database
- . mysql_error());
}
function query($query)
{
if (empty($query))
throw new Exception('Error: Query string empty. Cannot
proceed.');
$this-result = mysql_query($this-query, $this-link);
if (!$this-result) // beware that putting this msg on screen is a
security hazard
throw new Exception('Database Error: Couldn\'t query - ' .
mysql_error() . - $query);
}
function escape($ref)
{
return mysql_real_escape_string($ref, $this-link);
}
}
A couple of thoughts. First precede all your mysql_* calls with the at
sign (@) to shut up the routines if they generate text. I had this
problem, and that was the answer.
yes that's very bad advice. using error suppression is bad for performance
and debugging, don't do it unless you really really have to (e.g. you
have some function that spits warnings even with display_errors set to Off)
display_errors should be set to Off in production, and errors/warnings shouldn't
be suppressed, they should be logged.
handle
Re: [PHP] MySQL class. Thoughts?
On January 21, 2009 12:37:07 Jay Moore wrote:
This is a MySQL class I use and I wanted to get everyone's thoughts on
how/if I can improve it. This is for MySQL only. I don't need to make
it compatible with other databases. I'm curious what you all think.
I definetly think that using a DB class is a really good idea, but the
interface to your object could use some improvement.
Sample usage:
$value = 'value';
$sql = new do_mysql();
$sql-sanitize($value);
$sql-query = SELECT * FROM `wherever` WHERE `field` = '$value';
$sql-process('dbname');
$sql-close();
if ($sql-num_rows())
{
while ($row = $sql-fetch_array())
{
do stuff;
}
}
One of the goals of encapsulating DB access should be to hide some of the
tediousness of database access, however, using the interface for your object
you're still writing just as many (if not more) lines of code as you would by
simply using the mysql_* functions directly.
As an example of how you can put this into practice, here's the interface to
the DB object that I use:
The object is implemented as a singleton. When retrieved initially the
constructor deals with setting up the connection using constant defined in the
class for username, password, dbName and server address. The instance itself
wraps a PDO connection and provides the following interface:
// Query functions
query($query, $queryName = null, $taskName = null);
prepare($query, $queryName, $taskName = null);
execute($queryName, array $params = array(), $taskName = null);
static sanitize($string);
// Data retrieval functions
getResults($queryName, $taskName = null);
getColumn($queryName, $taskName = null, $columnIndex = 0);
getRow($queryName, $taskName = null, $rowIndex = 0);
getCell($queryName, $taskName = null, $rowIndex = 0, $columnIndex = 0);
// Storage control
reset($queryName, $taskName = null);
resetAll($taskName = null);
The interface is clean and lets me handle most common cases with readable
code:
// Retrieve one piece of data from the database
try {
$db = DB::getInstance();
$db-prepare('SELECT COUNT(*) FROM users WHERE id=:userid',
'getNumUsers');
$db-execute('getNumUsers', array(':userid' = $userId));
$numUsers = $db-getCell('getNumUsers');
// Retrieve a row from the database
if($numUsers == 1) {
$db-prepare('SELECT * FROM users WHERE id=:userid',
'getUserInfo');
$db-execute('getUserInfo', array(':userid' = $userId));
$userInfo = $db-getRow('getUserInfo');
// Retrieve multiple rows from the data
$db-prepare('SELECT friend_id FROM user_friends WHERE
user_id=:userid',
'getUsersFriends');
$db-execute('getUsersFriends', array(':userid' = $userId));
foreach($db-getColumn('getUsersFriends') AS $friendId) {
$db-execute('getUserInfo', array(':userid'=
$friendId));
$friendInfo = $db-getRow('getUserInfo');
// .
}
}
$db-reset('getNumUsers');
$db-reset('getUserInfo');
$db-reset('getUsersFiends');
} catch(DBException $exception) {
echo $exception;
}
Since the object wraps a PDO object, there's no need to sanitize, just
parameterize anything that may be unsafe in a prepared statement. The
retrieval functions will all return the data in a form that's easy to access
so you don't need to worry about getting an associative array when all you
want is a single cell or worry about getting a table when all you want is a
single row. This helps to reduces clutter in your code. Any PDOExceptions
are wrapped with the DBException class that implements an __toString method
that outputs a nice message to make error output clean and consistent.
The interface for you object does little more than replace the mysql_*
function calls with calls to an equivalent function in your oject.
Re-writing your example from above:
$db = DB::getInstance();
$value = 'value';
$db-prepare('SELECT * FROM wherever WHERE field=:val, 'getData');
$db-execute('getData', array(':val' = $value));
foreach($db-getResults('getData') AS $row) {
// Do stuff
}
Resulting in a reduction of 5 lines of code. I may not sound that impressive
for a small example but over the number of times you generally access a
database it will really add up.
--
Philip Graham
Lightbox Technologies
www.lightbox.org
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Good ideas guys. The input is much appreciated.
Jochem (and anyone else, I guess), as I am not 100% versed with
Exceptions, the php5 version you suggested, are those Exceptions able to
be handled outside the class?
Do I need my try block to be within the class block, or can I have the
try block be in my normal code where I actually instantiate the class?
This:
class blah
{
try { stuff } catch (exception) { more stuff }
}
$i = new blah()
or can I do this:
class blah
{
do some stuff (no try/catch blocks here)
throw an exception
}
try
{
$i = new blah();
more stuff
}
catch (exception) { even more stuff }
Thanks,
Jay
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 09:10:54PM +0100, Jochem Maas wrote:
Paul M Foster schreef:
On Wed, Jan 21, 2009 at 11:37:07AM -0600, Jay Moore wrote:
This is a MySQL class I use and I wanted to get everyone's thoughts on
how/if I can improve it. This is for MySQL only. I don't need to make
it compatible with other databases. I'm curious what you all think.
I'd try to move to using the mysqli functions or class, which
you can still wrap in a custom object (I do this because
I like to minimize the interface to the bare, bare minimum ...
professional laziness you might say).
does you class need to be php4 compatible? I'd hope
not but you may still have to support php4 ... even then
I'd doubt you'd be using php4 for new project so it
might be worth making a new php5 only class.
I always write with PHP4 in mind. You never know. Besides, I can always
change the internal implementation, if it's a class.
snip
A couple of thoughts. First precede all your mysql_* calls with the at
sign (@) to shut up the routines if they generate text. I had this
problem, and that was the answer.
yes that's very bad advice. using error suppression is bad for performance
and debugging, don't do it unless you really really have to (e.g. you
have some function that spits warnings even with display_errors set to Off)
display_errors should be set to Off in production, and errors/warnings
shouldn't
be suppressed, they should be logged.
handle errors gracefully ('or die()' is not graceful)
I don't know about performance. But every call to the query() method of
my class tests to see the results of the query. If it failed, I call the
proper function from PostgreSQL to fetch the error, and store it in the
class. The query function returns false, if there's an error. So the
user can test the return and then call a function to echo the stored
error.
Second, store your connection resource as a class variable, so you can
pass it around to the various routines. Actually, you're already doing
this, but I prefer to do so explicitly, as:
var $link;
that's very php4.
Yep.
at the top of the class.
I have a similar class for PostgreSQL. I also have routines like
update, which allow you to pass a table name and an associative array
of field values. Same thing for an insert routine.
if the postgres extension is anything like the firebird extension then
there may actually be a few calls which do require error suppression :-)
Probably. When I first started coding PHP/PostgreSQL, I was getting
stuff printed out from the pg_* routines on the webpages and screwing
things up. I was on the PostgreSQL list at the time, and sent an email
to the list about chatter from the pg_* routines. The solution was to
prefix them with @. Works great, but I *do* check for and capture error
text and such.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 02:30:00PM -0600, Jay Moore wrote:
Good ideas guys. The input is much appreciated.
Jochem (and anyone else, I guess), as I am not 100% versed with
Exceptions, the php5 version you suggested, are those Exceptions able to
be handled outside the class?
Do I need my try block to be within the class block, or can I have the
try block be in my normal code where I actually instantiate the class?
This:
class blah
{
try { stuff } catch (exception) { more stuff }
}
$i = new blah()
or can I do this:
class blah
{
do some stuff (no try/catch blocks here)
throw an exception
}
try
{
$i = new blah();
more stuff
}
catch (exception) { even more stuff }
I know it's very OO-y to use exceptions, but I hate them. They're like
setjmp/longjmp calls in C, and they're a really headache to deal with.
If you don't use default or predone handlers, you have to put all kinds
of try/catch blocks around everything. They make for non-linear
execution, and I prefer my code to execute in a linear fashion.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Jay Moore schreef:
Good ideas guys. The input is much appreciated.
Jochem (and anyone else, I guess), as I am not 100% versed with
Exceptions, the php5 version you suggested, are those Exceptions able to
be handled outside the class?
Do I need my try block to be within the class block, or can I have the
try block be in my normal code where I actually instantiate the class?
This:
class blah
{
try { stuff } catch (exception) { more stuff }
}
$i = new blah()
no, this does not work, 'snot even valid syntax.
or can I do this:
class blah
{
do some stuff (no try/catch blocks here)
throw an exception
}
try
{
$i = new blah();
more stuff
}
catch (exception) { even more stuff }
yes.
try {
$db = new do_mysql('root', 'pass', 'mydb');
} catch (Exception $e) {
// var_dump($e); // debug
// could not connect, deal with it.
}
// do some stuff
try {
$db-query($myQry);
} catch (Exception $e) {
// bad query, deal with
}
... etc, etc
Thanks,
Jay
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
I know it's very OO-y to use exceptions, but I hate them. They're like setjmp/longjmp calls in C, and they're a really headache to deal with. If you don't use default or predone handlers, you have to put all kinds of try/catch blocks around everything. They make for non-linear execution, and I prefer my code to execute in a linear fashion. Paul My thoughts exactly. What do I gain by using a try/catch that I lose by using if/else or similar? J -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Jay Moore schreef:
I know it's very OO-y to use exceptions, but I hate them. They're like
setjmp/longjmp calls in C, and they're a really headache to deal with.
If you don't use default or predone handlers, you have to put all kinds
of try/catch blocks around everything. They make for non-linear
execution, and I prefer my code to execute in a linear fashion.
Paul
My thoughts exactly. What do I gain by using a try/catch that I lose by
using if/else or similar?
you use them not for control flow, but for deferring exceptional application
states, which you can then handle in a small number of places as opposed
to scattering the error handling of unlikely events in all sorts of
disparate places.
there is an art to using them, they compliment 'traditional' error handling,
and I agree they can hinder if used badly.
NB: you can have multiple catch blocks:
try {
// do lots of stuff
} catch (ConnectException $e) {
} catch (QueryException $e) {
} catch (DBSelectException $e) {
} catch (Exception $e) {
// catch all for stuff we don't handle specifically
}
J
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
2009/1/21 Jay Moore jaymo...@accu-com.com I know it's very OO-y to use exceptions, but I hate them. They're like setjmp/longjmp calls in C, and they're a really headache to deal with. If you don't use default or predone handlers, you have to put all kinds of try/catch blocks around everything. They make for non-linear execution, and I prefer my code to execute in a linear fashion. this also means you use a single return in a function ? Paul My thoughts exactly. What do I gain by using a try/catch that I lose by using if/else or similar? the power to omit it, and then change your mind lather, and handle them. The only problem is that php itself (nor core, nor extensions) throw exceptions, so you can't threat errors uniformly, but you cant have a custom error handler, and throw exceptions from there, and you can also have an exception handler. I have used this approach in several projects, and i admit it takes some getting used to, but it's an easy, clean, nice way to handle exceptions / errors / conditions. J -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Torok, Alpar Istvan
Re: Re: [PHP] MySQL class. Thoughts?
there is an art to using them, they compliment 'traditional' error handling, and I agree they can hinder if used badly. I don't think I've ever seen Exceptions used well... Invariably, I end up having to write a wrapper function around every function implemented and catch all the Exceptions. Otherwise, my code is littered with try/catch blocks for every little thing the other guy was too lazy to figure out how to handle gracefully. ymmv -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
c...@l-i-e.com wrote:
there is an art to using them, they compliment 'traditional' error
handling, and I agree they can hinder if used badly.
I don't think I've ever seen Exceptions used well...
Invariably, I end up having to write a wrapper function around every function
implemented and catch all the Exceptions.
Otherwise, my code is littered with try/catch blocks for every little thing the
other guy was too lazy to figure out how to handle gracefully.
ymmv
i use them often, basically if a boolean false won't do its a case of
throwing an exception.
let's say you have:
calls_fifty_methods($page_load_of_variables);
wrap that bit in a try catch and you get
try {
calls_fifty_methods($page_load_of_variables);
} catch ( DatabaseException $e) {
// handle that error
} catch ( FileNotFoundException $e) {
// handle
} catch ( VerySpecificException) {
// handle
} catch ( Exception $e ) {
// didn't expect this, notify devs, error log it and do X Y Z
}
try firing back error codes or something from 50 methods down and you
have a real can go wrong easily series of returning error codes and
processing the same anyways; or you could take the echo some html
approach from the function - which is wrong on so many levels or..
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 10:00:53PM +0100, Jochem Maas wrote: Jay Moore schreef: I know it's very OO-y to use exceptions, but I hate them. They're like setjmp/longjmp calls in C, and they're a really headache to deal with. If you don't use default or predone handlers, you have to put all kinds of try/catch blocks around everything. They make for non-linear execution, and I prefer my code to execute in a linear fashion. Paul My thoughts exactly. What do I gain by using a try/catch that I lose by using if/else or similar? you use them not for control flow, but for deferring exceptional application states, which you can then handle in a small number of places as opposed to scattering the error handling of unlikely events in all sorts of disparate places. there is an art to using them, they compliment 'traditional' error handling, and I agree they can hinder if used badly. I understand, but then it comes down to how you define an exceptional application state. If I build a date class that throws exceptions, it's very OO-y, but a waste of my programmer time. I could just as easily build an error checker into the class and use that to check for errors. Generally speaking, my next step is going to be to display the page again and tell the user to retype the date properly anyway. There are reasons to use setjmp and longjmp in C as well, but I only ever used them once, and later ripped out that code. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Nathan Rixham schreef:
c...@l-i-e.com wrote:
there is an art to using them, they compliment 'traditional' error
handling, and I agree they can hinder if used badly.
I don't think I've ever seen Exceptions used well...
Invariably, I end up having to write a wrapper function around every
function implemented and catch all the Exceptions.
Otherwise, my code is littered with try/catch blocks for every little
thing the other guy was too lazy to figure out how to handle gracefully.
ymmv
i use them often, basically if a boolean false won't do its a case of
throwing an exception.
let's say you have:
calls_fifty_methods($page_load_of_variables);
wrap that bit in a try catch and you get
try {
calls_fifty_methods($page_load_of_variables);
} catch ( DatabaseException $e) {
// handle that error
} catch ( FileNotFoundException $e) {
// handle
} catch ( VerySpecificException) {
// handle
} catch ( Exception $e ) {
// didn't expect this, notify devs, error log it and do X Y Z
}
try firing back error codes or something from 50 methods down and you
have a real can go wrong easily series of returning error codes and
processing the same anyways; or you could take the echo some html
approach from the function - which is wrong on so many levels or..
agreed, that basically the point, being able to defer the handling of
the problem to some code that knows what to do with it given the context,
if a DB object cannot connect to the DB how does it know what to do in
terms of handling the situation? ... in a webpage you may wish to
redirect, show an error msg or something else (which shouldn't be up
to the object in question and should therefore not be built into it),
in a CLI environment you'd want to do something else maybe.
wrapper functions to handle exceptions as Richard offered is actually
quite neat assuming that the wrapper functions are designed for a
given context (and that they can then assume to be able to handle
the given exception in a way suitable to the given context)
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Eric Butera wrote: On Wed, Jan 21, 2009 at 12:45 PM, Edmund Hertle edmund.her...@student.kit.edu wrote: 2009/1/21 Jay Moore jaymo...@accu-com.com This is a MySQL class I use and I wanted to get everyone's thoughts on how/if I can improve it. This is for MySQL only. I don't need to make it compatible with other databases. I'm curious what you all think. Thanks, Jay Hey, 1. You know the mysqli-Class? 2. If yes, than I don't get it in which way this will improve mysql handling -eddy Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
I prefer to deal with the issues locally, or have a documented behaviour with return values and error details available, much like most of PHP extensions/internals. try/catch ends up with weird code organization, imho, especially when you can only really handle some exceptions. For real fun, one API layer will catch all the lower exceptions, then wrap them in a generic exception, and you have to string parse $e-getMessage() to figure out what to *do*. [shudder] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 5:53 PM, Chris dmag...@gmail.com wrote: Eric Butera wrote: On Wed, Jan 21, 2009 at 12:45 PM, Edmund Hertle edmund.her...@student.kit.edu wrote: 2009/1/21 Jay Moore jaymo...@accu-com.com This is a MySQL class I use and I wanted to get everyone's thoughts on how/if I can improve it. This is for MySQL only. I don't need to make it compatible with other databases. I'm curious what you all think. Thanks, Jay Hey, 1. You know the mysqli-Class? 2. If yes, than I don't get it in which way this will improve mysql handling -eddy Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). -- Postgresql php tutorials http://www.designmagick.com/ That's done by tail -f /var/log/mysql/query.log. :D -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). That's done by tail -f /var/log/mysql/query.log. :D That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 6:09 PM, Chris dmag...@gmail.com wrote: Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). That's done by tail -f /var/log/mysql/query.log. :D That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. -- Postgresql php tutorials http://www.designmagick.com/ I've been using it for 5 years now and haven't had problems. Then again I still write sql by hand too. I only use it when something is acting really weird and I'm having a hard time. So it is a targeted process where I know what I'm looking for. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
Chris schreef: Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). That's done by tail -f /var/log/mysql/query.log. :D That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. besides on shared hosting that log is often turned off even if you can get at it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 6:37 PM, Jochem Maas joc...@iamjochem.com wrote: Chris schreef: Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). That's done by tail -f /var/log/mysql/query.log. :D That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. besides on shared hosting that log is often turned off even if you can get at it. That's why I set up a local dev environment. If something is wrong, just grab a db dump figure it out locally. That way I can do whatever I need to really try out what the issue is and the best way to resolve it. Just merely saying how I develop. Whatever gets it done is the real way. :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, 2009-01-21 at 18:52 -0500, Eric Butera wrote: On Wed, Jan 21, 2009 at 6:37 PM, Jochem Maas joc...@iamjochem.com wrote: Chris schreef: Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). That's done by tail -f /var/log/mysql/query.log. :D That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. besides on shared hosting that log is often turned off even if you can get at it. That's why I set up a local dev environment. If something is wrong, just grab a db dump figure it out locally. That way I can do whatever I need to really try out what the issue is and the best way to resolve it. Just merely saying how I develop. Whatever gets it done is the real way. :) A DB dump won't always tell you where the problem lies. With proper logging, you can use the DB dump to work out what went wrong precisely. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL class. Thoughts?
On Wed, Jan 21, 2009 at 7:07 PM, Ashley Sheridan a...@ashleysheridan.co.uk wrote: On Wed, 2009-01-21 at 18:52 -0500, Eric Butera wrote: On Wed, Jan 21, 2009 at 6:37 PM, Jochem Maas joc...@iamjochem.com wrote: Chris schreef: Yea if you're only targeting 1 db, then why not use that class? At least then there's the php manual to figure out what something does. Because then to add query logging for the whole app, you just need to put it in the class :) (I've done that before to check what's being run and where from, comes in very handy). That's done by tail -f /var/log/mysql/query.log. :D That won't tell you where a query comes from ;) Add a debug_backtrace into the class to also pinpoint where the query was called from. Complicated queries built on variables (or even just long queries built over multiple lines) will be hard to find just by looking at the mysql query log. besides on shared hosting that log is often turned off even if you can get at it. That's why I set up a local dev environment. If something is wrong, just grab a db dump figure it out locally. That way I can do whatever I need to really try out what the issue is and the best way to resolve it. Just merely saying how I develop. Whatever gets it done is the real way. :) A DB dump won't always tell you where the problem lies. With proper logging, you can use the DB dump to work out what went wrong precisely. Ash www.ashleysheridan.co.uk I meant grab a remote db dump, import it locally, reproduce the issue locally, look at local query log, fix. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL client version problem
PHP had a built-in MySQL for awhile as I recall. You had to explicitly use --with-mysql=/usr/local (or wherever you put your mysql headers/libs) to make it choose the one you wanted. Even if it's not using built-in, it may have found an old install of your MySQL rather than your shiny new 5.0.45 version. config.log will tell you what happened. config.nice will tell you what you typed for ./configure You definitely will want to fix this before you do anything else. hth -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL client version problem
Thing is, I have MySQL 5.0.67 installed, and I've never had MySQL 4 on this box (Xserve G5, Mac OS X Server 10.4.11). Here's my configure command: ./configure --prefix=/usr/local/php5 --mandir=/usr/share/man -- infodir=/usr/share/info --with-apxs --with-ldap=/usr --with-kerberos=/ usr --enable-cli --with-zlib-dir=/usr --with-libxml-dir=/usr --enable- exif --enable-ftp --enable-mbstring --enable-sockets --enable-fastcgi --with-iodbc=/usr --with-curl=/usr --with-config-file-path=/private/ etc --with-mysql=/usr --with-mysql-sock=/var/mysql/mysql.sock If I remove --mysql=/usr, MySQL doesn't work at all (not surprising I guess). Is there something else I need to do to compile PHP with the latest MySQL module? (Which I don't understand--is the module part of the PHP source, or is it something external that gets linked to?) ...Rene On 5-Jan-09, at 10:13 AM, c...@l-i-e.com wrote: PHP had a built-in MySQL for awhile as I recall. You had to explicitly use --with-mysql=/usr/local (or wherever you put your mysql headers/libs) to make it choose the one you wanted. Even if it's not using built-in, it may have found an old install of your MySQL rather than your shiny new 5.0.45 version. config.log will tell you what happened. config.nice will tell you what you typed for ./configure You definitely will want to fix this before you do anything else. hth -- 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] MySQL client version problem
Historically, mysql has been both an external extension, and a built-in part of the source, depending on the version of PHP. Your current experience would indicate that it's only external in 5.2.8, but I cannot confirm nor deny that. How is MySQL installed? If it's rpm, do you have mysql-devel or whatever also installed? You need this to get /usr/include/mysql* so PHP can link to your /usr/lib/mysql* files. If you ask for MySQL, but don't provide the headers, ./configure MAY be trying to use an old internal built-in mysql source from php source tree. This is just a theory. less config.log /mysql will tell you more accurately than anything what happened where -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
The mysql forum is the best place. Note that their holiday schedule may mean some lag in getting answers. Bastien Sent from my iPod On Dec 29, 2008, at 7:51 AM, ann kok oiyan...@yahoo.ca wrote: Hi all Do you know any websites for mysql question? I do submit the mysql forum but I would like to have more to learn Now I have mysql replication question. Thank you __ Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com -- 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] mySQL query question
Jim Lucas wrote:
[EMAIL PROTECTED] wrote:
Ok, so just that I am clear, you are SELECTing and pulling all the data
that you are submitting in the above INSERT statement from the DB
initially,
then you are only modifying the confirm_number value and then re-
submitting all the values, as they originally were,
Well, actually when all is said and done, a new record will be created with
new information (Name, phone, email, etc) and the confirm_number is the
previous+1
Seems like a perfect candidate for an auto-inc field, though mysql
doesn't let you have multiple in the same table (afaik).
# Now prepare your statement
$SQL =
SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`);
INSERT INTO `contacts` (
`first_name`,
`last_name`,
`email`,
`phn_number`,
`address`,
`city`,
`state`,
`zip`,
`dates`,
`comments`,
`confirm_number`
) VALUES (
'{$FirstName}',
'{$LastName}',
'{$Email}',
'{$Phone}',
'{$Address}',
'{$City}',
'{$selected_state}',
'{$Zip}',
'{$newdate}',
'{$Comments}',
@confirm_number
)
SELECT @confirm_number AS confirm_number;
;
You do have a race condition, you can end up with 2 of the same
confirm_numbers (you'd have to be unlucky, but it can happen).
2 hits at the same time = 2 selects getting the same
max(confirm_number), which results in 2 inserts with the same number.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Chris wrote: Jim Lucas wrote: [EMAIL PROTECTED] wrote: Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 Seems like a perfect candidate for an auto-inc field, though mysql doesn't let you have multiple in the same table (afaik). I would agree, but I'm not the OP. He/She wanted it this way... You do have a race condition, you can end up with 2 of the same confirm_numbers (you'd have to be unlucky, but it can happen). 2 hits at the same time = 2 selects getting the same max(confirm_number), which results in 2 inserts with the same number. Granted that their is a possibility that it could happen. But the chance of it happening with the three statements running back-to-back in the same call is much lower then having three separate calls and doing the math in PHP. -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage wrote: On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? The idea is to give you the number that was used in the INSERT statement. It might have changed since the INSERT. Never know. So, giving you the one used in the INSERT is the best way to make sure you get the one you are expecting. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 08:46 +0100, Jochem Maas wrote: 1000 + 1 != 10001 you might consider setting a default of 1000 or 1 or whatever on the given field so it's automatically populated with that number when a contact record is created. Sorry. Hit the 0 one to few times. -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; I assume that you are already aware that if you set the variable $queryconfirm to this SQL query that the query is not necessarily executed and returns the result into the variable. $confirmresult=$queryconfirm; Now what you did is that $confirmresult is now: SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1 as well as $queryconfirm. Why is that? now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; I will also assume that you don't think that $confirm_number will magically contain the result. Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? If what I assume is right (I don't want to disappoint you) but you should start reading some PHP basics on how you make database connection in PHP. Use google to find some tutorial for e.g. If I am wrong post us the code to see if someone can help. -- Thodoris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
I would create a separate table for this (confirmation_numbers or something) with an autoincrement primary key. That way you can simply insert a new record for you contact and then ask (using mysql_insert_id()) what the confirmation number is. This approach is much safer as you can be 100% sure the number is unique and it's much less complicated. (of course you still need to check if the query didn't fail) - http://devshed.excudo.net http://devshed.excudo.net -- View this message in context: http://www.nabble.com/mySQL-query-question-tp20495466p20501473.html Sent from the PHP - General mailing list archive at Nabble.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, Nov 14, 2008 at 9:58 AM, [EMAIL PROTECTED] wrote: okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database No, you don't want to do that. :-) You are introducing a race condition between TWO users who hit the same page at the same time. They each get, say, 42, and they each put back 43, but one of them should have been 44. What you WANT to do is this: update contacts set confirm_number = confirm_number + 1 order by contact desc limit 1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php yep, our current app, designed by 'brighter minds' than mine, refused to make these auto numbers and now we have problems caused by the race condition. -- Bastien Cat, the other other white meat
Re: [PHP] mySQL query question
update contacts set confirm_number = confirm_number + 1 order by
contact
desc limit 1
Here is the php query I've been using to send the record in the first
place
$query=INSERT INTO contacts (first_name, last_name, email, phn_number,
address, city, state, zip, dates, comments, confirm_number) VALUES
('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City',
'$selected_state', '$Zip', '$newdate', '$Comments' ,
'$confirm_number' );
[EMAIL PROTECTED] ($query);
(obviously in the script, it's all on one line)
Now, what I need to do, is somehow pull make confirm_number get
submitted as a new record, which will happen once they submit the form,
but I want it to be submitted +1. (12345 should now be 12346 but a new
record entirely)
I was trying this code before the submission query:
$confirmnumber=SELECT confirm_number from contacts ORDER BY contact
DESC LIMIT 1;
$confirm_number=$confirmnumber+1;
Now what this is doing for me so far, is just taking the first numeral
of the record, which happens to be 4 (I originally added the
confirm_number via the rand function, but have since taken that out) and
adding 1 to it and that is it.
So the new $confirm_number=5
So it sort of did the job, but not quite..
The question is how can I make a new record (I know the answer to that
part) BUT with a confirm_number of 1 greater than the previous record.
--
Michael S. Dunsavage
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
[EMAIL PROTECTED] wrote:
update contacts set confirm_number = confirm_number + 1 order by
contact
desc limit 1
Here is the php query I've been using to send the record in the first
place
$query=INSERT INTO contacts (first_name, last_name, email, phn_number,
address, city, state, zip, dates, comments, confirm_number) VALUES
('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City',
'$selected_state', '$Zip', '$newdate', '$Comments' ,
'$confirm_number' );
[EMAIL PROTECTED] ($query);
(obviously in the script, it's all on one line)
Now, what I need to do, is somehow pull make confirm_number get
submitted as a new record, which will happen once they submit the form,
but I want it to be submitted +1. (12345 should now be 12346 but a new
record entirely)
I was trying this code before the submission query:
$confirmnumber=SELECT confirm_number from contacts ORDER BY contact
DESC LIMIT 1;
$confirm_number=$confirmnumber+1;
Now what this is doing for me so far, is just taking the first numeral
of the record, which happens to be 4 (I originally added the
confirm_number via the rand function, but have since taken that out) and
adding 1 to it and that is it.
So the new $confirm_number=5
So it sort of did the job, but not quite..
The question is how can I make a new record (I know the answer to that
part) BUT with a confirm_number of 1 greater than the previous record.
Ok, so just that I am clear, you are SELECTing and pulling all the data that
you are submitting in the above INSERT statement from the DB initially,
then you are only modifying the confirm_number value and then re-submitting all
the values, as they originally were, back to the DB and creating a
copy of the original data. The only difference being that the $confirm_number
has been altered. Correct?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, Nov 14, 2008 at 1:22 PM, [EMAIL PROTECTED] wrote:
update contacts set confirm_number = confirm_number + 1 order by
contact
desc limit 1
Here is the php query I've been using to send the record in the first
place
$query=INSERT INTO contacts (first_name, last_name, email, phn_number,
address, city, state, zip, dates, comments, confirm_number) VALUES
('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City',
'$selected_state', '$Zip', '$newdate', '$Comments' ,
'$confirm_number' );
[EMAIL PROTECTED] ($query);
(obviously in the script, it's all on one line)
Now, what I need to do, is somehow pull make confirm_number get
submitted as a new record, which will happen once they submit the form,
but I want it to be submitted +1. (12345 should now be 12346 but a new
record entirely)
I was trying this code before the submission query:
$confirmnumber=SELECT confirm_number from contacts ORDER BY contact
DESC LIMIT 1;
$confirm_number=$confirmnumber+1;
Now what this is doing for me so far, is just taking the first numeral
of the record, which happens to be 4 (I originally added the
confirm_number via the rand function, but have since taken that out) and
adding 1 to it and that is it.
So the new $confirm_number=5
So it sort of did the job, but not quite..
The question is how can I make a new record (I know the answer to that
part) BUT with a confirm_number of 1 greater than the previous record.
--
Michael S. Dunsavage
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
make that field an autonumber and let the database assign it. much much
cleaner and simple to get the number with mysql_last_insert()
--
Bastien
Cat, the other other white meat
Re: [PHP] mySQL query question
Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 This whole thing is a contact form. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
[EMAIL PROTECTED] wrote:
Ok, so just that I am clear, you are SELECTing and pulling all the data
that you are submitting in the above INSERT statement from the DB
initially,
then you are only modifying the confirm_number value and then re-
submitting all the values, as they originally were,
Well, actually when all is said and done, a new record will be created with
new information (Name, phone, email, etc) and the confirm_number is the
previous+1
This whole thing is a contact form.
Well, in that case, you might be able to do something along the lines of this.
I tested this on my server:
Server version: 5.0.51a-log
MySQL client version: 5.0.51a
using phpMyAdmin - 2.11.1.2
I have modified an example from this page:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
?php
#
# Setup database stuff, process input, get everything ready to do the insert.
#
# Now prepare your statement
$SQL =
SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`);
INSERT INTO `contacts` (
`first_name`,
`last_name`,
`email`,
`phn_number`,
`address`,
`city`,
`state`,
`zip`,
`dates`,
`comments`,
`confirm_number`
) VALUES (
'{$FirstName}',
'{$LastName}',
'{$Email}',
'{$Phone}',
'{$Address}',
'{$City}',
'{$selected_state}',
'{$Zip}',
'{$newdate}',
'{$Comments}',
@confirm_number
)
SELECT @confirm_number AS confirm_number;
;
$confirm_number = NULL;
# Run it and get confirm_number to work with now.
if ( ($result = @mysql_query($SQL)) !== FALSE ) {
list($confirm_number) = mysql_fetch_row($result);
}
if ( is_null($confirm_number) ) {
echo 'Failed to get number';
}
?
Obviously, I can't test this without your schema. So, I hope it works.
In the end, you should have a result set that gets returned that contains the
'confirm_number' of the newly created entry.
This should also, pretty much, eliminate any chance of a race condition. Since
everything is happening within mysql, it should be very hard to end up
with a condition that you start stomping on records.
Let the list know if it works for you.
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Jim Lucas wrote:
[EMAIL PROTECTED] wrote:
Ok, so just that I am clear, you are SELECTing and pulling all the data
that you are submitting in the above INSERT statement from the DB
initially,
then you are only modifying the confirm_number value and then re-
submitting all the values, as they originally were,
Well, actually when all is said and done, a new record will be created with
new information (Name, phone, email, etc) and the confirm_number is the
previous+1
This whole thing is a contact form.
Well, in that case, you might be able to do something along the lines of this.
I tested this on my server:
Server version: 5.0.51a-log
MySQL client version: 5.0.51a
using phpMyAdmin - 2.11.1.2
I have modified an example from this page:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
?php
#
# Setup database stuff, process input, get everything ready to do the insert.
#
# Now prepare your statement
$SQL =
SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`);
INSERT INTO `contacts` (
`first_name`,
`last_name`,
`email`,
`phn_number`,
`address`,
`city`,
`state`,
`zip`,
`dates`,
`comments`,
`confirm_number`
) VALUES (
'{$FirstName}',
'{$LastName}',
'{$Email}',
'{$Phone}',
'{$Address}',
'{$City}',
'{$selected_state}',
'{$Zip}',
'{$newdate}',
'{$Comments}',
@confirm_number
)
The above should be this instead
@confirm_number
);
SELECT @confirm_number AS confirm_number;
;
$confirm_number = NULL;
# Run it and get confirm_number to work with now.
if ( ($result = @mysql_query($SQL)) !== FALSE ) {
list($confirm_number) = mysql_fetch_row($result);
}
if ( is_null($confirm_number) ) {
echo 'Failed to get number';
}
?
Obviously, I can't test this without your schema. So, I hope it works.
In the end, you should have a result set that gets returned that contains the
'confirm_number' of the newly created entry.
This should also, pretty much, eliminate any chance of a race condition.
Since everything is happening within mysql, it should be very hard to end up
with a condition that you start stomping on records.
Let the list know if it works for you.
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote:
'{$Comments}',
@confirm_number
)
The above should be this instead
@confirm_number
);
Even after fixing that, nothing gets inserted into the database. I've
been all over the variables and column names and the naming is correct.
--
Michael S. Dunsavage
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage wrote:
On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote:
'{$Comments}',
@confirm_number
)
The above should be this instead
@confirm_number
);
Even after fixing that, nothing gets inserted into the database. I've
been all over the variables and column names and the naming is correct.
Take the @ off the mysql_query() and also check into mysql_error() function.
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Jim Lucas wrote:
Michael S. Dunsavage wrote:
On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote:
'{$Comments}',
@confirm_number
)
The above should be this instead
@confirm_number
);
Even after fixing that, nothing gets inserted into the database. I've
been all over the variables and column names and the naming is correct.
Take the @ off the mysql_query() and also check into mysql_error() function.
also, try it with a striped down version of the insert, just inserting the
confirm_number
INSERT INTO contacts (confirm_number) VALUES (@confirm_number);
and see if that creates a new record.
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
If you're just adding one, there is no reason to retrieve the data, process it, and update it. You can just update the number. http://dev.mysql.com/doc/refman/5.0/en/update.html Also, you should read the MySQL manual on default values: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Michael S. Dunsavage wrote: okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirmresult=$queryconfirm; now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 00:52 -0600, Micah Gersten wrote: If you're just adding one, there is no reason to retrieve the data, process it, and update it. You can just update the number. http://dev.mysql.com/doc/refman/5.0/en/update.html But, the problem is that the confirm_number is a confirmation number that gets e-mailed out. So I have to pull it from the DB any way -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage schreef: okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirmresult=$queryconfirm; now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to AFAIKT your not querying the DB at all and your merely adding 1 to a string, which results in 1. adding 1 to a php variable will never cause and update in a database ... well actually there might be a way to do that but I can't think of it using some kind of hyper funky object but I'm pretty sure there is no way to overload the + operator. confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? 1000 + 1 != 10001 you might consider setting a default of 1000 or 1 or whatever on the given field so it's automatically populated with that number when a contact record is created. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP/mySQL question using ORDER BY with logic
Rob Gould schrieb: Question about mySQL and PHP, when using the mySQL ORDER BY method... Basically I've got data coming from the database where a wine producer-name is a word like: Château Bahans Haut-Brion or La Chapelle de La Mission Haut-Brion or Le Clarence de Haut-Brion but I need to ORDER BY using a varient of the string: 1) If it begins with Château, don't include Chateau in the string to order by. 2) If it begins with La, don't order by La, unless the first word is Chateau, and then go ahead and order by La. Example sort: Notice how the producer as-in comes before the parenthesis, but the ORDER BY actually occurs after a re-ordering of the producer-string, using the above rules. Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château ) Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission Haut-Brion, La ) Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le ) Red: Château Haut-Brion (Haut-Brion, Château ) Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château ) Red: Domaine de La Passion Haut Brion (La Passion Haut Brion, Domaine de ) Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château ) Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château ) Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château ) That logic between mySQL and PHP, I'm just not sure how to accomplish? I think it might involve a mySQL alias-technique but I could be wrong. Right now, my PHP call to generate the search is this: $query = 'SELECT * FROM wine WHERE MATCH(producer, varietal, appellation, designation, region, vineyard, subregion, country, vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT 0,100'; Hi, Try to solve your Logic on your programming language and to select Data with your Database... Try to normalize more your Information on the Database. Regars Carlos -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP/mySQL question using ORDER BY with logic
Robert Cummings wrote: On Fri, 2008-10-24 at 00:18 -0400, Rob Gould wrote: Question about mySQL and PHP, when using the mySQL ORDER BY method... Basically I've got data coming from the database where a wine producer-name is a word like: Château Bahans Haut-Brion or La Chapelle de La Mission Haut-Brion or Le Clarence de Haut-Brion but I need to ORDER BY using a varient of the string: 1) If it begins with Château, don't include Chateau in the string to order by. 2) If it begins with La, don't order by La, unless the first word is Chateau, and then go ahead and order by La. Example sort: Notice how the producer as-in comes before the parenthesis, but the ORDER BY actually occurs after a re-ordering of the producer-string, using the above rules. Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château ) Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission Haut-Brion, La ) Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le ) Red: Château Haut-Brion (Haut-Brion, Château ) Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château ) Red: Domaine de La Passion Haut Brion (La Passion Haut Brion, Domaine de ) Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château ) Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château ) Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château ) That logic between mySQL and PHP, I'm just not sure how to accomplish? I think it might involve a mySQL alias-technique but I could be wrong. Right now, my PHP call to generate the search is this: $query = 'SELECT * FROM wine WHERE MATCH(producer, varietal, appellation, designation, region, vineyard, subregion, country, vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT 0,100'; Maybe there's a good way to do it with the table as is... but I'm doubtful. I would create a second field that contains a pre-processed version of the name that performs stripping to achieve what you want. This could be done by a PHP script when the data is inserted into the database, or if not possible like that, then a cron job could run once in a while, check for entries with this field empty and generate it. Yeah I'd suspect that the storage overhead is nothing compared to the speed increase you'll get during the read operations if you don't have to dick around with the data :) (yes I'm comparing bits to time, but I don't have time to explain that bit). Col -- Colin Guthrie gmane(at)colin.guthr.ie http://colin.guthr.ie/ Day Job: Tribalogic Limited [http://www.tribalogic.net/] Open Source: Mandriva Linux Contributor [http://www.mandriva.com/] PulseAudio Hacker [http://www.pulseaudio.org/] Trac Hacker [http://trac.edgewall.org/] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search
At 11:16 PM -0700 10/21/08, Ryan S wrote: clipp Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Do it right... read up on MySQL's fulltext matching. Cheers, Rob. /clipp Did some searching based on your tip, got what i was looking for, just didnt know where to start.. and now feeling like the man who was taught how to fish :D Thanks! R I often feel like a fish given a matchbook. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search
clipp Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Do it right... read up on MySQL's fulltext matching. Cheers, Rob. /clipp Did some searching based on your tip, got what i was looking for, just didnt know where to start.. and now feeling like the man who was taught how to fish :D Thanks! R -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search
On Tue, 2008-10-21 at 21:48 -0700, Ryan S wrote: Hey all, I have two columns in my DB title varchar(254) and jtext text which I would like to search, as the user might enter two or more words I am opting not to use LIKE %search_term% so started searching google, I came across this very promising class: http://code.activestate.com/recipes/125901/ but when i tried to run it I am just getting a blank page, no errors or anything. Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Do it right... read up on MySQL's fulltext matching. 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] MYSQL insert problems
On Sat, Oct 18, 2008 at 3:22 AM, Frank Stanovcak [EMAIL PROTECTED] wrote: I'm using the following code to try and do a simple insert query. However it won't insert the data into the table, and I get no error messages. What have I done wrong this time? You will be getting an error. echo mysql_error(); DateShipped,Quantity, Cases, Pallets, Weight, FKUSShippedBy, BillofLading,) Extra comma at the end of BillofLading. Also you should really use mysql_real_escape_string for non-numeric values, and at least check stuff like $_SESSION['quantity'] is a number. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Workbench coming for Linux
Ross McKay schreef: Posting this here, because a few people responded when I mentioned not having a Linux-native data modelling tool. Apparently, MySQL Workbench should be alpha-ready by end of the month... http://dev.mysql.com/workbench/?p=138 no news of MacOSX, what's the chance this will build on that? Maybe I can ditch Visio one day soon... :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP-MYSQL Error: Can't connect to MySQL socket. Can someone helpme out please?
By the way it installed MySQL 6 and PHP 5.0.4 and from the console this command does not work: mysql -u root -p but only this works: mysql -h hostname -u root -p I tried doing the same while connecting to the database via php but it does not work. Rahul wrote: I am using Fedora Core 4. As I was unable to use PHP or MySQL together, I uninstalled both of them and installed again using the following commands: yum install mysql And then apt-get install php php-devel php-gd php-imap php-ldap php-mysql php-odbc php-pear php-xml php-xmlrpc curl curl-devel perl-libwww-perl ImageMagick And then started the mysql server. I am able to connect to the server from the console my typing mysql -h hostname -u root -p mypass However, when I try to connect to mysql through PHP I get the following errors: PHP Warning: mysql_query(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /export/home/rahul/may/sample.php on line 5 PHP Warning: mysql_query(): A link to the server could not be established in /export/home/rahul/may/sample.php on line 5 Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Can someone please help me out? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Hi Jim Lucas, You are correct... i want to run in the same way. but as my 2 tables, column name are different i cant run the LOAD DATA infile. And the example you mentioned for break at 100, also i thought to use in that way. but one of the column had the text type which we cant predict about the size. Thanks for the support from all of you. Now, I am inserting the rows one by one only On 5/2/08, Chris [EMAIL PROTECTED] wrote: Jim Lucas wrote: Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? Doesn't have to. http://dev.mysql.com/doc/refman/5.0/en/load-data.html By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); But load data infile requires extra mysql privileges. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Sanjeev http://www.sanchanworld.com | http://webdirectory.sanchanworld.com - submit your site
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N wrote: Hi Jim Lucas, You are correct... i want to run in the same way. but as my 2 tables, column name are different i cant run the LOAD DATA infile. If you're inserting the same data, then use LOAD DATA INFILE to load it into a temporary table, then use INSERT SELECT's to put them into the other tables. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Jim Lucas wrote: Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? Doesn't have to. http://dev.mysql.com/doc/refman/5.0/en/load-data.html By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); But load data infile requires extra mysql privileges. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N [EMAIL PROTECTED] wrote:
Hi,
I have written a program which imports the tab delimited file and insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting time with
a single statement instead of writing more insert statements
Sure, check with a MySQL list via http://www.mysql.com
Otherwise, split it up into multiple inserts.
I currently have a script which reads a 550,000 CSV file and uses
?php
// read file
// for each line, do the following
while ($in != EOF)
{
$line=explode (',',$in);
$out=insert into table values('','$line[0]', .);
fwrite ($outf, $out);
}
then I have another file that
?php
//read the file line by line
//connect to db
$sql = $inLine;
mysql_query($sql) or die();
?
which all in all takes about 10 seconds to run the conversion and then the
inserts.
HTH,
Wolf
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
?php
if ( count($array) ) {
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
$data = array();
foreach ( $array AS $row ){
$row_clean = array_map('mysql_real_escape_string', $row);
$data[] = ('{$row_clean[1]}', '{$row_clean[2]}',.'{$row_clean[6]}');
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die(query failed : .mysql_errror());
} else {
echo Nothing to insert;
}
?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Jim Lucas wrote:
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and
insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is
its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting
time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
?php
if ( count($array) ) {
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
$data = array();
foreach ( $array AS $row ){
$row_clean = array_map('mysql_real_escape_string', $row);
$data[] = ('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}');
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
} else {
echo Nothing to insert;
}
?
That would work, but will probably result in a query string that is too long.
I'll redo the above to fix that.
?php
# How often do you want to insert??
$break_at = 100;
# Initialize the counter
$cnt = 0;
# Initial insert string
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
# if there is data, then process, otherwise skip it.
if ( count($array) ) {
$data = array();
# Loop through data
foreach ( $array AS $row ) {
$cnt++;
# Clean the result data
$row_clean = array_map('mysql_real_escape_string', $row);
# Build data string and push it onto the data array.
$data[] = ('{$row_clean[1]}', '{$row_clean[2]}',.'{$row_clean[6]}');
# Break and insert if we are at the break point
if ( $cnt === $break_at ) {
# Reset Counter
$cnt = 0;
# Run insert
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
# Reset data array
$data = array();
} //if
} //foreach
# This should take care of any extra that didn't get processed in the foreach
if ( count($data) ) {
# Insert remaining data
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
} //if
} else {
echo Nothing to insert;
} //if
?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Wouldn't using LOAD DATA INFILE be better than writing your own script?
On 5/1/08, Jim Lucas [EMAIL PROTECTED] wrote:
Jim Lucas wrote:
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and
insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is
its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $i++){
$insert_string .= insert into tablename (v1, v2. v6)
values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');;
}
if(!empty($insert_string)){
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with SET GLOBAL max_allowed_packet=3000;
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting
time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
?php
if ( count($array) ) {
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
$data = array();
foreach ( $array AS $row ){
$row_clean = array_map('mysql_real_escape_string', $row);
$data[] = ('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}');
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die(query failed :
.mysql_errror());
} else {
echo Nothing to insert;
}
?
That would work, but will probably result in a query string that is too
long.
I'll redo the above to fix that.
?php
# How often do you want to insert??
$break_at = 100;
# Initialize the counter
$cnt = 0;
# Initial insert string
$insert_string = INSERT INTO tablename (v1, v2. v6) VALUES ;
# if there is data, then process, otherwise skip it.
if ( count($array) ) {
$data = array();
# Loop through data
foreach ( $array AS $row ) {
$cnt++;
# Clean the result data
$row_clean = array_map('mysql_real_escape_string', $row);
# Build data string and push it onto the data array.
$data[] = ('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}');
# Break and insert if we are at the break point
if ( $cnt === $break_at ) {
# Reset Counter
$cnt = 0;
# Run insert
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
# Reset data array
$data = array();
} //if
} //foreach
# This should take care of any extra that didn't get processed in the
foreach
if ( count($data) ) {
# Insert remaining data
mysql_query($insert_string . join(', ', $data), $conn) or
die(query failed : .mysql_error());
} //if
} else {
echo Nothing to insert;
} //if
?
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
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] mysql joins
On Tue, Mar 25, 2008 at 8:20 AM, Steven Macintyre [EMAIL PROTECTED] wrote: I have three tables, namely; User - UID - Firstname - Surname - Tel - Cell - Email Tracker - UID - Points Winners - UID - Datetime (-00-00 00:00:00) I need to get the following information from the above tables (in my logical sense) All users from user with sum(points) as points and datetime datetime + 14 days In English, the all users must be selected, excluding the ones that have won in the last 14 days and return all the information and the sum of points I suspect I would need to use joins here ... but have no clue how to do so ... I have read up a bit and can work out inner joins from three tables, but not coping with this problem above Can someone help me out with this please? Many thanks Steven See what mileage this gets you. SELECT User.UID, FirstName, Surname, Tel, Cell, Email, SUM(Points) AS TotalPoints FROM User INNER JOIN Tracker ON User.UID = Tracker.UID LEFT OUTER JOIN Winners ON User.UID = Winners.UID WHERE`Datetime` DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL -14 DAY) OR`Datetime` IS NULL GROUP BY User.UID, FirstName, Surname, Tel, Cell, Email The OUTER JOIN and the last line (OR `Datetime` IS NULL) is there so that your query will include results for users who have never won. I don't think it this is optimized (or how you could do so if needed) since the IS NULL condition will probably make the query use a table scan rather than an index. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql joins
Steven Macintyre [EMAIL PROTECTED] wrote: I have three tables, namely; User - UID - Firstname - Surname - Tel - Cell - Email Tracker - UID - Points Winners - UID - Datetime (-00-00 00:00:00) I need to get the following information from the above tables (in my logical sense) All users from user with sum(points) as points and datetime datetime + 14 days In English, the all users must be selected, excluding the ones that have won in the last 14 days and return all the information and the sum of points I suspect I would need to use joins here ... but have no clue how to do so ... I have read up a bit and can work out inner joins from three tables, but not coping with this problem above Can someone help me out with this please? Many thanks Steven What PHP code have you written so far? I personally would do a query on one table then use the results to grab the information from the other table. Some use joins, but I haven't seen a ton of time loss between the multi-short calls versus a join.. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql test and error
On Sun, Feb 24, 2008 at 5:00 PM, hE [EMAIL PROTECTED] wrote:
The following program gave the error:
Parse error: parse error in C:\apache\htdocs\mysqltest.php on line 10
Look at this part of the code:
$result = mysql_query($sql);
if ($result == 0)
echo 'bError ' . mysql_errno() . ': '. mysql_error() . '/b';
else
{
The error message gave you the answer. You don't have the proper
curly brackets for your if/else statements. Replace the above with
this:
$result = mysql_query($sql);
if ($result == 0) {
echo 'bError ' . mysql_errno() . ': '. mysql_error() . '/b';
} else {
html
headtitleTest MySQL/title/head
body
!-- mysql_up.php --
?php
$host=localhost;
$user=root;
$password=;
mysql_connect($host,$user,$password);
$sql=show status;
$result = mysql_query($sql);
if ($result == 0)
echo 'bError ' . mysql_errno() . ': '. mysql_error() . '/b';
else
{
?
!-- Table that displays the results --
table border=1
trtdbVariable_name/b/tdtdbValue/b
/td/tr
?php
for ($i = 0; $i mysql_num_rows($result); $i++) {
echo 'TR';
$row_array = mysql_fetch_row($result);
for ($j = 0; $j mysql_num_fields($result); $j++)
{
echo 'TD'. $row_array[$j] . '/td';
}
echo '/tr';
}
?
/table
/body/html
what is the reason?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql vs. Mysqli crash handling
On Mon, Feb 25, 2008 at 3:07 PM, Larry Garfield [EMAIL PROTECTED] wrote:
Hi folks. I've an odd issue.
Only fair. You're an odd bird, and we're an odd bunch. ;-P
If I connect to a MySQL DB using ext/mysql, and for whatever reason the
process dies (uncaught exception, fatal error, etc.) the connection is
garbage collected and closed. If, however, I use ext/mysqli, the connection
remains open forever and just eats up resources, eventually resulting in
hitting the connection limit. Same app, same database, same server.
What version of PHP and MySQL (client extension and server) are you using?
What do your mysqli_query() command and SQL query string look like?
Any idea why mysqli behaves that way, and how to make it clean up properly?
(Yes I should of course try to avoid fatals in the first place, but when they
do happen I don't want them to bring the whole server to its knees.)
I may not be able to help you, since I've only recently started
switching myself over to mysqli (I know, as always I'm late to adopt),
but with more information, maybe someone like Richard Lynch can come
in and work a miracle.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql vs. Mysqli crash handling
On Monday 25 February 2008, Daniel Brown wrote: On Mon, Feb 25, 2008 at 3:07 PM, Larry Garfield [EMAIL PROTECTED] wrote: Hi folks. I've an odd issue. Only fair. You're an odd bird, and we're an odd bunch. ;-P If I connect to a MySQL DB using ext/mysql, and for whatever reason the process dies (uncaught exception, fatal error, etc.) the connection is garbage collected and closed. If, however, I use ext/mysqli, the connection remains open forever and just eats up resources, eventually resulting in hitting the connection limit. Same app, same database, same server. What version of PHP and MySQL (client extension and server) are you using? Yeah, I should have mentioned that... PHP 5.1.6, MySQL 5.0.48 (client and server I think; I'm not the sysadmin). What do your mysqli_query() command and SQL query string look like? I'll have to ask our sysadmin for the test scripts he ran. (We're running the site on Drupal, which can handle either, but has a lot of DB abstraction involved. He tested it sans-Drupal, but I am not sure what his scripts were.) Any idea why mysqli behaves that way, and how to make it clean up properly? (Yes I should of course try to avoid fatals in the first place, but when they do happen I don't want them to bring the whole server to its knees.) I may not be able to help you, since I've only recently started switching myself over to mysqli (I know, as always I'm late to adopt), but with more information, maybe someone like Richard Lynch can come in and work a miracle. Hi Rich! :-) -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql test and error
On Feb 24, 2008, at 1135AM, hE wrote:
hi,
I found an e-book in the net about php and mysql and with its sample
program I am trying to test whether I have mysql working correctly
or not. the following program gives error message. why?
What exactly does the error message say? We can help troubleshoot
errors much better with error messages. :)
html
headtitleTest MySQL/title/head
body
!-- mysql_up.php --
?php3
Change ?php3 to ?php - everywhere in your code.
By the fact that it's using ?php3 I'm guessing this is a very old
tutorial. I'd strongly suggest using a more recent tutorial. There
have been lots of changes since PHP3 and there's no reason to start
with such an old version.
Here are some places to start:
http://devzone.zend.com/article/627-PHP-101-PHP-For-the-Absolute-Beginner
http://hudzilla.org/phpwiki/index.php?title=Main_Page
I also strongly suggest taking some time to go through the manual.
Browse through the different sections and get an idea for what is
available. Obviously I'm not suggesting you memorize the manual, but
I've found it very helpful to occasionally browse through a section or
two in the manual, I always seem to find functions I wasn't aware of
before. This really helps in deciding the best way to solve a problem.
if ($result == 0)
echo “bError “ . mysql_errno() . “: “
. mysql_error() . “/b”;
Is this all on one line in your code? If not it needs to be inside
curly braces, though this could be just a case of long lines be broken
up by your email software. For consistency and readability, especially
when starting with a new language, I tend to always use curly braces -
even if not needed.
if ($result == 0)
{
echo 'bError ' . mysql_errno() . ': '. mysql_error() . '/b';
}
else
{
?
!-- Table that displays the results --
table border=”1”
trtdbVariable_name/b/tdtdbValue/b
/td/tr
?php3
for ($i = 0; $i mysql_num_rows($result); $i++) {
echo “TR”;
$row_array = mysql_fetch_row($result);
for ($j = 0; $j mysql_num_fields($result); $j++)
{
echo “TD” . $row_array[$j] . “/td”;
}
echo “/tr”;
}
?
One last suggestion, indent your code. For example:
else
{
?
!-- Table that displays the results --
table border=”1”
trtdbVariable_name/b/tdtdbValue/b/td/tr
?php
for ($i = 0; $i mysql_num_rows($result); $i++)
{
echo 'TR';
$row_array = mysql_fetch_row($result);
for ($j = 0; $j mysql_num_fields($result); $j++)
{
echo 'TD'. $row_array[$j] . '/td';
}
echo '/tr';
}
?
/table
?php
}
?
While it's not necessary, I find it much easier to read and
troubleshoot code when indented.
Brady
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql test and error
The following program gave the error:
Parse error: parse error in C:\apache\htdocs\mysqltest.php on line 10
html
headtitleTest MySQL/title/head
body
!-- mysql_up.php --
?php
$host=”localhost”;
$user=”root”;
$password=””;
mysql_connect($host,$user,$password);
$sql=”show status”;
$result = mysql_query($sql);
if ($result == 0)
echo 'bError ' . mysql_errno() . ': '. mysql_error() . '/b';
else
{
?
!-- Table that displays the results --
table border=”1”
trtdbVariable_name/b/tdtdbValue/b
/td/tr
?php
for ($i = 0; $i mysql_num_rows($result); $i++) {
echo 'TR';
$row_array = mysql_fetch_row($result);
for ($j = 0; $j mysql_num_fields($result); $j++)
{
echo 'TD'. $row_array[$j] . '/td';
}
echo '/tr';
}
?
/table
/body/html
what is the reason?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql test and error
On Feb 24, 2008, at 200PM, hE wrote: The following program gave the error: Parse error: parse error in C:\apache\htdocs\mysqltest.php on line 10 Did you copy/paste the code? If so maybe the quotation marks are the fancy smart quotes like MS Office likes to use. Try replacing your quotes with regular old quotation marks. Brady -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql input
I agree, but they all provide some level of handling just might not be the 'most correct' way of handling it bastien Subject: RE: [PHP] mysql input From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; php-general@lists.php.net Date: Mon, 18 Feb 2008 23:31:21 -0500 On Mon, 2008-02-18 at 23:19 -0500, Bastien Koert wrote: mysql_real_escape_string() addslashes() htmlentities() take your pick That's a bad answer. If he's using MySQL then he SHOULD use mysql_real_escape_string(). None of the other functions will fully protect him from malicious input. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' _
Re: [PHP] mysql input
On Mon, February 18, 2008 10:05 pm, nihilism machine wrote: I have a user saving a VARCHAR(255) field in a mysql db which has single quotes in the text, how can i replace them so that they dont fuck up my mysql command? http://php.net/mysql_real_escape_string Google for SQL injection for (way) more info. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql input
On Mon, February 18, 2008 10:19 pm, Bastien Koert wrote: mysql_real_escape_string() Yes. addslashes() No, not right for different charsets. See above. htmlentities() Completely and wildly inappropriate. Might as well use a cannon to slice a tomato. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Stored Procedures
my only wish was that more people wrote more articles about the proper structure. You mean like the example on the mysql website? http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql input
On Mon, 2008-02-18 at 23:05 -0500, nihilism machine wrote: I have a user saving a VARCHAR(255) field in a mysql db which has single quotes in the text, how can i replace them so that they dont fuck up my mysql command? mysql_real_escape_string() Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql input
On Mon, 2008-02-18 at 23:19 -0500, Bastien Koert wrote: mysql_real_escape_string() addslashes() htmlentities() take your pick That's a bad answer. If he's using MySQL then he SHOULD use mysql_real_escape_string(). None of the other functions will fully protect him from malicious input. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql input
mysql_real_escape_string() addslashes() htmlentities() take your pick bastien From: [EMAIL PROTECTED] To: php-general@lists.php.net Date: Mon, 18 Feb 2008 23:05:10 -0500 Subject: [PHP] mysql input I have a user saving a VARCHAR(255) field in a mysql db which has single quotes in the text, how can i replace them so that they dont fuck up my mysql command? -e -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _
Re: [PHP] mysql question
On Sun, February 10, 2008 11:52 am, Per Jessen wrote: nihilism machine wrote: $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) return stripslashes($ret); else return $ret; } what is $ret, an array? No, it's a mysql result object. No, it's a single field value from the database. $r is the result object. http://php.net/mysql_result -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question #2
At any rate, just seeing this tells me that you've got a real mess on your hands... Or you could say, You're going to have some fun cleaning that. -- Richard Heyes http://www.websupportsolutions.co.uk Knowledge Base and Helpdesk software hosted for you - no installation, no maintenance, new features automatic and free ** New Helpdesk demo now available ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question #2
On Sun, February 10, 2008 12:12 pm, nihilism machine wrote:
public function select_one($sql) {
if ($this-auto_slashes) {
return stripslashes($ret);
If you have to call stripslashes() on data coming FROM MySQL, then you
have really messed up...
You've put in data that was escaped TWICE, probably with Magic Quotes
ON followed by addslashes (or mysql_real_escape_string).
At any rate, just seeing this tells me that you've got a real mess on
your hands...
} else {
return $ret;
}
}
how can i get the contents of a column in the returned row say for
something called Email as the column name. here is my code now:
Since it's only returning ONE piece of data, how confused can it be?
$this-whatever['Email'] = $result;
--
Some people have a gift link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
On Feb 10, 2008 1:03 PM, Per Jessen [EMAIL PROTECTED] wrote:
Yep, you're right - I read mysql_query where the OP said mysql_result.
Don't feel bad. I did the exact same thing when I was reading
over the post just now.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
On Feb 10, 2008 12:52 PM, Per Jessen [EMAIL PROTECTED] wrote: nihilism machine wrote: $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) return stripslashes($ret); else return $ret; } what is $ret, an array? No, it's a mysql result object. no, its the contents of the first cell in the first record of the result set; from the doc on mysql_result(), http://www.php.net/manual/en/function.mysql-result.php which is what the function is using. if so how can i access the individual rows in it? this method does not return a result set to the caller. -nathan
Re: [PHP] mysql question
nihilism machine wrote: $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) return stripslashes($ret); else return $ret; } what is $ret, an array? No, it's a mysql result object. if so how can i access the individual rows in it? Look up mysql_fetch_assoc(). /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
