Re: [PHP-DB] Problem with PDO Mysql and FETCH::ASSOC
Hello, I am using the following code to perform queries on a MySQL database: $dbh = new PDO($GLOBALS['database'],$GLOBALS['username'],$GLOBALS['password']); $dbh-setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $dbh-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $query = $dbh-prepare(SELECT * FROM log); $query-execute(); $results = $query-fetchALL(PDO::FETCH_ASSOC); $dbh = null; If I perform this query in MySQL, I get: mysql select * from log; +-++---+--+++ | type| date | client_id | model_id | request| message| +-++---+--+++ | message | 1.2375e+09 | domain.18052 | | client_start | started client | However, if I perform this query with the PHP code above, I get: Array ( [type] = error [date] = 1.2375e+09 [log] = [distributed] = client_start [def] = started client) ) which is clearly wrong. 'distributed' is actually the name of the database, so I don't really know what it is doing as a key in the above result. If I use PDO::FETCH_BOTH instead of PDO::FETCH_ASSOC, I get Array ( [type] = message [0] = message [date] = 1.2375e+09 [1] = 1.2375e+09 [log] = [2] = domain.18052 [3] = [distributed] = client_start [4] = client_start [def] = started client [5] = started client ) which *does* contain the correct values with the numerical keys. Before using MySQL, I was using SQLite, and this problem did not occur. The description of the table is: mysql describe log; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | type | char(10) | YES | | NULL| | | date | float| YES | | NULL| | | client_id | char(50) | YES | | NULL| | | model_id | char(50) | YES | | NULL| | | request | char(20) | YES | | NULL| | | message | char(50) | YES | | NULL| | +---+--+--+-+-+---+ 6 rows in set (0.00 sec) Does anyone have any ideas as to what I might be doing wrong? Thanks for any help! Thomas First of all check if you are actually connecting to the same database both times. Start with printing $GLOBALS['database'] and see if it connects where you really want. You mentioned a field def while printing the query's output which obviously is not included in the table's description. Have you changed the schema in the meantime? -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] PDO buffered query problem
Stewart Duncan wrote: Hi there, I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?). Here is my code to reproduce the problem: ?php $Database = new PDO( 'mysql:host=localhost;port=3306;dbname=mydatabase', 'root', '', array( PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true ) ); Don't you want it the other way around? You want it unbuffered so it doesn't get retrieved in mysql, send the whole result set to php then you can use it. You want to start using it immediately - so make it unbuffered. Either way if the result set is going to be large your MySQL's memory or PHP's memory may exceed. So if you use either you may need to fine-tune PHP by increasing the per process memory (memory_limit in php.ini) or MySQL. In case you use unbuffered queries you cannot use transactions as far as I can recall. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Update SQL Statements Execution
Hi, I am inquiring on this list to see if it is possible to create a script that takes multiple update statements without my having to write one SQL statement for each of the updates. If you want to reuse the same SQL query by just changing the parameters you may consider using the PDO's driver for mssql an make prepared statements. Never used MsSQL so I am not sure that prepared statements work the same as with other PDO drivers. check this on the manual: http://www.php.net/manual/en/pdo.prepare.php You may also find instructions on how to install and use PDO and the drivers it support on the manual as well. I have a scenario of which I create a table of some sort with some existing information using Flex, and what I am told by my client is that no matter how many records there are on the screen, the users should be able to update any up to all the entries by simply pushing a button. I use Microsoft SQL, which I think that it does allow multiple update query execution. The problem is that I might have to come up with some method to accept all the POST variables the user provides into the script. You can do that by using the $_POST array which stores all the posted data. check this: http://www.php.net/manual/en/reserved.variables.post.php Could anyone please give me some guidance on what kind of function I might use, or whether or not it is possible I can create a script that accepts as many POST variables as the users POST? Thanks a lot for your help. Alice Those two elementary howtos may be of some help: http://www.w3schools.com/php/php_post.asp http://www.tizag.com/phpT/postget.php If all these seem too elementary for you then try to describe your problem in a more detailed manner. Better questions get better answers. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] trying to get pecl and phpize to build oci8
I have Fedoara 2.6.27.5-37.fc9.i686 that has perl: This is perl, v5.10.0 built for i386-linux-thread-multi I searched the entire system and found neither pecl or phpize Although this not a php question try: yum install php-devel -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Copying Data From One Table To Another Table
Alice Wei wrote:
Hi,
I am trying to accomplish a task of which I have to copy the data
from one table to another. The problem is that whenever I tried to
perform the action of copying the data through this method, although
the data gets copied, it performs reiterative copying that causes my
program and my server to crash.
Here is the code snippet:
#Empty Temporary Array
$tmpHolder = array();
$tmpHolder2 = array();
#Loop through parts list
foreach ($stringChunk1 As $part) {
#Create a single statement and stuff it in your tmp array
$tmpHolder[]=incidence.name LIKE '%{$part}';}
foreach ($stringChunk2 As $part2) {
#Create a single statement and stuff it in your tmp array
$tmpHolder2[]=regions.name LIKE '%{$part2}';}
#Join all the parts together, placing an OR between each element
$string3= join(' OR ', $tmpHolder);
$string2= join(' OR ', $tmpHolder2);
$total=$count_chunk1 * $count_chunk2;
//Entry a New Query Entry into scenarios table
$query3=INSERT INTO scenarios VALUES('$scenario_name');
$result3=mssql_query($query3) or die (The query has failed);
#Search for the Regions and Incidence ID $query_both=SELECT
incidence.ID,regions.ID from incidence,regions WHERE ($string3)AND
($string2);$result_both=mssql_query($query_both) or die (The
query has failed);
while($row_both = mssql_fetch_array($result_both)) {
$incidence_id= $row_both[0];
$region_id= $row_both[1];
//Enter entry into scenario elements table
// $query14=INSERT INTO
scenario_elements(region_id,incidence_id,market,number_sales,number_defer,customer_satisfaction)
// SELECT
region_id,incidence_id,market,number_sales,number_defer,customer_satisfaction
FROM scenario_elements_2,regions,incidence WHERE $string2 AND $string3;
// $result14=mssql_query($query14) or die (The query has failed);
I have to comment out the code snippet above because it creates
something like 3 entries to the database. Could anyone please
tell me if this is the error I have from my PHP, or is it from my SQL
statement? How would I go about fixing this issue?
An insert into select doesn't need to use a loop, you can use just one
query:
insert into scenarios(field1, field2) select field1, field2 from
other_table where
the db will (internally) loop over the results and do the work.
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
Not to mention that you don't even need PHP for that. You just need to
open a client and make the query.
--
Thodoris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Copying Data From One Table To Another Table
$result3=mssql_query($query3) or die (The query has failed); snip http://dev.mysql.com/doc/refman/5.0/en/insert-select.html Ah - the OP isn't using mysql, it's ms-sql. Oups didn't notice that :-) . -- Thodoris
Re: [PHP-DB] Building additional modules after installing core modules
I decided to install php from source. I have installed the core modules only. Is that possible to install additional modules 〔such as, gd, ming bcmath etc〕after installing php core modules without compiling the entire source code? Try not to top-post. If you need the flexibility to add additional extensions that are not modules installed using pecl it is better to use a package system. Most distros are including in their repositories php modules like php-gd, php-soap, php-mssql etc. Why compile if you don't need to. This of course can be used if compiling php for performance is not mandatory. If otherwise have in mind that you need not only to compile the modules against the same src tree but to configure the src tree the same way as the installed php. Thus you will have to keep the original src you used to install php on the first place. At least IMHO this is the best practice. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query Timeout program in PHP
Man- when the query is fired through the web interface- it rans on mysql server max_execution_time - wont' help evn I stop apache itself... Yes it will. Apache will be instructed to stop execution of any script by the mod_php module (assuming you are using mod_php). But mysql process probably won't stop running (not 100% sure for that) although php script has timed out. The query runs on mysql server - so I have to kill the PID on server itself... In order to do this you will need (in the unix world) to have rights to kill mysql processes. That means that you must become either root or the mysql user which is not that simple since everything that apache runs is usually running as the apache or the www user who can't kill those processes (at least if he can't su exec). I will have to note here that this is a bad practice of doing things... Thanks Piyush On Thu, Oct 16, 2008 at 7:57 AM, Jack van Zanen [EMAIL PROTECTED] wrote: Just put the time out in your PHP.INI file max_execution_time = 30 ; Maximum execution time of each script, in seconds This is probably a solution although you will have to know what to expect if you query a table with 3M records. It will be slow and that can't change. Try using LIMIT when sending queries to stop mass data retrieval. 2008/10/16 Piyush Kumar [EMAIL PROTECTED] I'm using http://myclient.polarlava.com/ as web query interface for mysql server Now I want to add Query Timeout functionality to it Every apache process has a timeout limit you can leave the user wait for a page a lifetime. For that I need to get the PID for last ran mysql query and then using kill PID - I can kill the process on MySQL server Sorry but I still can't see why. Please explain how to do that in PHP Thanks! Similar to what described @ http://bytes.com/forum/thread156058.html -- Thanks Regards, -Piyush Mo.: 091-9910904233 Mail: [EMAIL PROTECTED] Web: http://piyush.me/ -In a world without fences, limits, boundaries and walls, Who needs Windows and Gates? Although some think that limits are wrong I still have walls around my house. Windows are not so bad sometimes everything has its use. Besides everyone needs to be annoyed from time to time. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- J.A. van Zanen -- Thodoris
Re: [PHP-DB] pagination php mysql
I am implementing pagination on my php script, pagination is already
working, the result is displayed as checkboxes for users to check their
choices. my prob is if i have more than 1 page, users should be able to
choose from all 4 pages,
one solution is creating a temporal table, which can be re-created and
dropped when starting and ending the user session (table name can be
an unique_ID). Then, the checkbox selections are inserted, deleted,
etc.. with mysql_functions every time the user change every page.
And how do you suggest that the data will be kept when the user goes to
page 2?
The session will end and the temporary table will be destroyed. So this
is probably not so wise don't you think ?
Another solution is adding the ID of every selected checkbox to a
cookie or session variable in format 1,5,42,87,412... etc. Then you
can manage the selections changes by reading the array elements using
a for... when the user change every page:
$foo = array_unique($foo); // checkbox selections
if (count($foo) 0) {
// loop through the array of selections
for ($i=0;$icount($foo);$i++) {
// read and write the session_var to update, delete, etc..
if (eregi($foo[$i], $session_var)) { // if exists ...etc
}
//...
}
}
Cookie is a good choice although if the checkboxes are many your cookie
will grow. Your problem here is how you can maintain data between pages
in order to keep all the users options. This problem has many solutions:
cookies, sessions, memcache, database etc.
The problem that you will a mechanism that will control the data.
When the user ends, you will have a temporal table or a session
variable with all his checkboxes selected.
Oh now I get it !! by temporary table you don't really mean a temporary
table like that:
http://archive.devwebpro.com/devwebpro-39-20010817Temporary-Tables-With-MySQL.html
but a table you will not use permanently. But AFAIK changing schema is
bad practice. Why not just keep the table and use it for this use?
First method is slower but more secure. Second is speediest but it can
have more failures because are session vars or cookies. It depends of
your control errors and also the visits profile.
I wouldn't bet on that I think it depends on the case.
Hope it helps,
--
Thodoris
Re: [PHP-DB] MySQL stored procedures OUT or INOUT parameters
: How to use OUT or INOUT parameters of MySQL's stored procedures in PHP? STF === http://eisenbits.homelinux.net/~stf/ . My PGP key fingerprint is: 9D25 3D89 75F1 DF1D F434 25D7 E87F A1B9 B80F 8062 === I think that the manual is quite clear on this: http://dev.mysql.com/doc/refman/5.1/en/call.html You use a way to query the database like PDO or mysqli and you wite sql. Is there something specific that you want to ask? -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about databases and foreign keys
On Sep 15, 2008, at 10:59 AM, Micah Gersten wrote: Use 2 tables. You never know what the app might grow into and you should do it right the first time. That's what I was thinking too... Just wanted to hear it from someone else... NOW I get to learn about foreign keys and how to update things with them! So if anyone knows of a good resource I'd appreciate it... Until then, I'm off to search the web and figure this stuff out! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 11287 James St Holland, MI 49424 www.raoset.com [EMAIL PROTECTED] This for mysql only but it can give some info you may find useful. This is a very enlightening article for starters in order to see the sql part. http://articles.techrepublic.com.com/5100-10878_11-6035435.html The mysql manual could also give you an idea on that. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO prepared statements and value list for MySQL IN
O/H TK ??:
At 03:21 AM 7/9/2008, Thodoris wrote:
At 04:16 PM 7/8/2008, Philip Thompson wrote:
On Jul 8, 2008, at 11:55 AM, TK wrote:
I'd like to use a PDO prepared statement to perform a MySQL query
that uses the IN function.
I.e.:
$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();
Here's the problem:
If $contents is set to a single value, everything's fine:
$contents = 'mystring';
How can I include multiple values in here? If $contents is set to
an array, PHP throws an Array to string conversion notice.
i.e. $contents = array('mystring1', 'mystring2');
If $contents is set to a comma-separated list, no matches are
returned (probably because the entire list is being passed to
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';
What's the proper way to do this? Can it be done? (Note that I do
not know how many elements will be in $contents ahead of time, so
something like IN (:contents1, :contents2) wouldn't make sense.)
$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);
I'm not sure if the IN function only works on numeric values or if it
also works on strings as well. If contents contains strings, you may
need to put single quotes around each item. If so, change the above
to.
$ins = ' . implode (',', $contents) . ';
Hope that helps,
Thanks. That's how to use the IN function in the first place, which I already
know. What I was asking about was how to do this using PDO and prepared
statements. It's the PDO prepared statement functionality that's got me stuck.
The issue is that I can't figure out how to bindParam or bindValue in this
situation, where there is potentially a list of values (of arbitrary size).
Perhaps the implode suggested above is the solution for the prepared statement.
I think that the problem is that you cannot bind a value with an array.
So you have to implode the array into a string before binding it to a value.
The other goes as you suggested:
$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();
Where contents is:
$pre_contents = array('mystring1', 'mystring2');
$contents = implode(',',$pre_contents);
or
$contents = 'mystring';
As per my original question, that does not work:
If $contents is set to a comma-separated list, no matches are
returned (probably because the entire list is being passed to
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';
Binding a comma-separated list (i.e. what implode creates) does not do what is wanted
here - it appears instead to treat the whole list as one entry, i.e. it's
like doing this:
where myfield IN ('mystring1,mystring2')
which is obviously not achieving the desired result of:
where myfield IN ('mystring1','mystring2')
Hence, my original question! Is there a way to accomplish this with PDO and
prepared statements?
- TK
Why don't you work this around. Since you may do anything with strings in php
using the (.) operator try this:
?php
$a = array('string1','string2');
$str = implode(',',$a);
$str = '.$str.';
print $str;
?
I will print out:
'string1','string2'
--
Thodoris
Re: [PHP-DB] PDO prepared statements and value list for MySQL IN
At 04:16 PM 7/8/2008, Philip Thompson wrote:
On Jul 8, 2008, at 11:55 AM, TK wrote:
I'd like to use a PDO prepared statement to perform a MySQL query
that uses the IN function.
I.e.:
$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();
Here's the problem:
If $contents is set to a single value, everything's fine:
$contents = 'mystring';
How can I include multiple values in here? If $contents is set to
an array, PHP throws an Array to string conversion notice.
i.e. $contents = array('mystring1', 'mystring2');
If $contents is set to a comma-separated list, no matches are
returned (probably because the entire list is being passed to
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';
What's the proper way to do this? Can it be done? (Note that I do
not know how many elements will be in $contents ahead of time, so
something like IN (:contents1, :contents2) wouldn't make sense.)
Thanks for any help!
- TK
$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);
I'm not sure if the IN function only works on numeric values or if it
also works on strings as well. If contents contains strings, you may
need to put single quotes around each item. If so, change the above
to.
$ins = ' . implode (',', $contents) . ';
Hope that helps,
~Philip
Thanks. That's how to use the IN function in the first place, which I already
know. What I was asking about was how to do this using PDO and prepared
statements. It's the PDO prepared statement functionality that's got me stuck.
The issue is that I can't figure out how to bindParam or bindValue in this
situation, where there is potentially a list of values (of arbitrary size).
- TK
Perhaps the implode suggested above is the solution for the prepared statement.
I think that the problem is that you cannot bind a value with an array.
So you have to implode the array into a string before binding it to a value.
The other goes as you suggested:
$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();
Where contents is:
$pre_contents = array('mystring1', 'mystring2');
$contents = implode(',',$pre_contents);
or
$contents = 'mystring';
--
Thodoris
Re: [PHP-DB] One field or a new table?
I am tempted to say that you can test both by benchmarking them. I assume that you need this to be fast since many users use it, so try both ways and see what it is better. Have in mind that the underlying engine is important when designing something that gets heavy use. I get the feeling that if the collectibles are few say 10-15 in that case adding a field tends to be a better idea. You can benchmark this by counting the time that you select and extract the collectibles in an array in either way. I assume that you will need to select from two tables user and collectibles in case you separate them. This is probably slower if the collectibles are few in number but I think it gets better when their number increases badly. Tell us what you think. -- Thodoris O/H Hussein Jafferjee ??: Hey Guys, I have been developing for a while and never found the best solution yet. The question is lets say a game has 10 collectibles you can earn, to keep track of the number of collectibles each user has, do you have one field in the users table with all the numbers separated via a divider, or do you make a new table called collectibles and have each one as a field? These are high traffic sites (100,000+ people) and so I was initially thinking the solution of creating a separate table is best because the main users row is loaded on every page, and on top of that you would need to use explode on the field. Currently I am having a separate table, but I was wondering if people have better solutions. Hussein J. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] explode () and mySQL query
O/H Ron Piggott ??: I am trying to add a search feature to my shopping cart. I am wanting to use PHP to develop the query for mySQL. I have the following code figured out: foreach(explode( , $keyword) as $key) $query .= $key, ; This produces: WHERE shopping_cart_product.product_description IN ( Jesus, is, Lord, ) The weakness is the trailing , after the last word the user types in. How can I eliminate this? Ron You can alternative use implode which I think is a much better choice because it does exactly what you need. You can use it like this $keys = implode(,,$keyword); $query .= $keys; This is the reference in the manual: http://gr2.php.net/manual/en/function.implode.php -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Wrong results with ORDER BY DESC
O/H Michael Kelly ??:
Hey all. I installed Apache and PHP onto my computer to use for
testing about six days ago, and have had MySQL installed for a few
weeks before. It all works fine, except for certain queries PHP gets
very odd and inconsistent results back.
Here's an example. This query, in both PHP and through the MySQL Query
Browser:
SELECT ID_FIRST_MSG FROM smf_topics WHERE ID_BOARD = 51 ORDER BY
ID_FIRST_MSG
Will work fine and return a 10-result set of the proper IDs from the
smf_topics table sorted correctly. However, this query, different only
by the addition of DESC:
SELECT ID_FIRST_MSG FROM smf_topics WHERE ID_BOARD = 51 ORDER BY
ID_FIRST_MSG DESC
Will work in the Query Browser fine, but not in PHP. When run from
PHP, it will return a 10-result set of an ID from other boards (same
table, different ID_BOARD values). All 10 rows will be the same ID,
and periodically the ID that is returned changes; sometimes it changes
as soon as the page is refreshed, sometimes it only changes after
waiting a bit, but there's no pattern I can see here.
Here's the PHP code I'm using to test it:
?php
mysql_connect('localhost','charas_forum2','');
mysql_select_db('charas_forum2');
$request = mysql_query(SELECT ID_FIRST_MSG FROM smf_topics WHERE
ID_BOARD = '51' ORDER BY ID_FIRST_MSG DESC);
while ($row = mysql_fetch_assoc($request)) {
echo $row['ID_FIRST_MSG'], 'br /';
}
?
I think that the ID_BOARD field should be an integer so you probably you
could leave unquoted (no harm with that). Check in the table what type
is the ID_FIRST_MSG because varchars order quite differently than
intergers. This I suspect this could be the mistake because I think that
you mean to use an integer.
In any case if you don't find what is wrong you can always put query
results in an array and sort them experimenting to see what is causing this.
My setup is as follows:
Windows Vista Business with Service Pack 1
Well you could always use a unix-like operating system but not all of us
are geeks.
Apache 2.2
PHP Version 5.2.6
MySQL 5.1.23-rc-community via TCP/IP
Any help would be greatly appreciated.
These are fresh so you shouldn't be having a problem.
--
Thodoris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] authentication verification
O/H DeadTOm ??: So the user comes to the site and they're presented with a log in page. They enter they're username and password and php checks a mysql database for a matching username and password. If there is a match, it responds with 1 line found. If no match, it responds with 0 lines found. In the case of a match, php then sets a cookie on their browser with a value of 1 for authenticated and 0 for not authenticated. Every subsequent page the user views checks the status of this cookie and if it's a zero it kicks them back to the log in page. This cookie expires in 5 days and after that they'll have to log in again. I'm aware that this is terribly easy to circumvent by creating/modifying a cookie with the 1 value and the site thinks you've passed muster. What is a better way of doing this? -- DeadTOm http://www.mtlaners.org [EMAIL PROTECTED] A Linux user since 1999. The alternative way is probably the sessions which is not really different is you plan to store just an authentication property of 0/1 into the cookie. The difference is that a session is created that stores some data like e-mail, frontend etc on the server and not in the cookie as described in the previous method. Although in order browser to track down the session sends to the server a session id which should be posted in the url or it can be set automatically by php in a cookie :-) . So you will need to put something in the cookie after all if you choose to do it this way but it would be a session id. However have in mind that you may have more control over sessions because you can configure your server to make great deal of things like say eliminate all sessions every say 5 minutes. I don't know if this was much of a help. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] character set collation
This may be of help I found that in mysql manual (http://dev.mysql.com/doc/refman/5.1/en/alter-database.html): To perform the task of upgrading database names with the new encoding, use |ALTER DATABASE /|db_name|/ UPGRADE DATA DIRECTORY NAME| instead (see Section 11.1.1, |ALTER DATABASE| Syntax http://dev.mysql.com/doc/refman/5.1/en/alter-database.html). But this is intended when upgrading to mysql 5.1 so if while upgrading to the new phpmyadmin you have updated mysql then this probably does it for you in any other situation try experimenting with the collation / encoding. PS Why don't you try replication instead of exporting / importing data?? -- Thodoris O/H elk dolk ??: Hi everybody, 1- I have created a database in the remote server and I have the same database in my local machine ,which is configured as IIS server. 2- MySQL server in my computer is configured as follows: Default-character-set=latin1 for CLIENT SECTION Default-character-set=latin1 for SERVER SECTION 3- I have populated my local database and tested it , it works fine. 4- Using 'SELECT' I put the data from my local database in a text file together with SQL commands and upload it to remote server (Import) 5- The above procedure was working well until they upgraded the phpMyAdmin to 2.8.0.1 6- The problem I am experiencing now is that: when I upload data to remote server using (Import)I see question marks ? instead of normal text in the 'description' and 'title' fields of the database. 7- It seems that it is character set collation problem but I can't figure it out! Any idea ? - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Re: [PHP-DB] How can I get MySQL protocol information with PDO?
O/H Kazuhiro IIzuka ??:
Thanks Thodoris.
But this is not things I desired.I've already read php manual and known
about PDO::getAttribute() before I post my question.
When I get to server version,I can use $dbh-getAttribute(PDO::
ATTR_SERVER_VERSION),but there are no attribute about mysql server
protocol.
I'm planing to make phpMyAdmin-clone with PDO.but I don't know PDO can
work instead of mysql/mysqli extension.and I couldn't find equivalent
function in PDO with mysql_get_proto_info(mysqli_get_proto_info).
// with mysql extension
$link = mysql_connect($host,$user,$password);
echo mysql_get_proto_info(); // returns 10 in my case.
but I cannot find the proper value like PDO::ATTR_SERVER_PROTOCOL in
PDO's getAttribute.
and PDO doesn't seems to use MySQL API's mysql_get_proto_info.
I check like this:
/path/to/php-5.2.4/ext$ grep -r 'mysql_get_proto_info' mysql
mysql/php_mysql.c: PHP_FE(mysql_get_proto_info, NULL)
mysql/php_mysql.c:/* {{{ proto int mysql_get_proto_info([int
link_identifier])
mysql/php_mysql.c:PHP_FUNCTION(mysql_get_proto_info)
mysql/php_mysql.c: RETURN_LONG(mysql_get_proto_info(mysql-conn));
mysql/php_mysql.h:PHP_FUNCTION(mysql_get_proto_info);
Binary file mysql/.libs/php_mysql.o matches
/path/to/php-5.2.4/ext$ grep -r 'mysql_get_proto_info' pdo*
/path/to/php-5.2.4/ext$
I forgot to tell you my environment.
MySQL version: 5.0.27-standard-log
PHP: 5.2.4 (upgrade in near future)
Apache: 2.2.6
You can retrieve some info for the mysql connection (server,driver etc)
using PDO::getAttribute()
For e.g. you can do something like that:
?php
$dbhost = 'localhost';
$dbuser = 'user_name';
$dbpass = 'password';
$db = 'database';
$dbh= new PDO('mysql:host='.$dbhost.';dbname='.$db, $dbuser, $dbpass);
echo $dbh-getAttribute(PDO::ATTR_DRIVER_NAME);
echo br;
echo $dbh-getAttribute(PDO::MYSQL_ATTR_MAX_BUFFER_SIZE);
echo br;
echo $dbh-getAttribute(PDO::ATTR_SERVER_VERSION);
echo br;
echo $dbh-getAttribute(PDO::ATTR_CONNECTION_STATUS);
?
Well Kazuhiro you should know that PDO is not exactly an alternative for
mysql/mysqli but it was supposed to abstract the database details under
an object. This is not totally yet happening but I think that you can
use PDO instead of mysql/mysqli in any case. Well I have wrote many
projects using it, and I think that if I wished to change the database
from mysql to some other it would take just few changes in my code.
You can always post this in the developers list if you want more details
on the matter and find out if any protocol attributes are to be supported.
--
Thodoris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How can I get MySQL protocol information with PDO?
O/H Kazuhiro IIzuka ??:
Hi All.
Now I'm trying to get MySQL protocol information with PDO.
There are mysql_get_proto_info and mysqli_get_proto_info to get
MySQL protocol information.
But PDO_MySQL doesn't seems to support this feature.
If I mistake,please let me know.and How to get MySQL protocol
information with *PDO*.
Thanks,
Kazu.
Although I have never used other methods to access mysql but PDO and I
am not sure what you mean. This could be the answer:
http://www.php.net/manual/en/ref.pdo-mysql.php
You can retrieve some info for the mysql connection (server,driver etc)
using PDO::getAttribute()
For e.g. you can do something like that:
?php
$dbhost = 'localhost';
$dbuser = 'user_name';
$dbpass = 'password';
$db = 'database';
$dbh= new PDO('mysql:host='.$dbhost.';dbname='.$db, $dbuser, $dbpass);
echo $dbh-getAttribute(PDO::ATTR_DRIVER_NAME);
echo br;
echo $dbh-getAttribute(PDO::MYSQL_ATTR_MAX_BUFFER_SIZE);
echo br;
echo $dbh-getAttribute(PDO::ATTR_SERVER_VERSION);
echo br;
echo $dbh-getAttribute(PDO::ATTR_CONNECTION_STATUS);
?
In order to find out driver details like the driver name, buffer size,
mysql version and connection status. A list of some of the the
attributes can be found in the link above or in the section of
Predefined Constants list in:
http://www.php.net/manual/en/ref.pdo.php
--
Thodoris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] parameters not working under PDO
O/H Chris Curvey ??:
It's quite possible that I'm missing something obvious here. The
following code fragment does not return any rows, but if I take out
the parameters and replace them with hardcoded strings (enclosed in
single quotes), I get the right results.
I've scattered print statements throughout, and the query seems to
get past execute() OK, it's just not returning anything from the call
to fetch()
Am I missing something obvious?
$stmt = $conn-prepare(select t.z from towns t
join counties c on t.county_z = c.z
where t.me = ?
and c.state_z = ?);
if ($stmt-execute($parts)) {
while ($row = $stmt-fetch()) {
$town_z = $row['z'];
}
What is $parts doing ?? You just need execute() and I suppose although
not posted that you did construct the object like:
$conn| = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
|A good choice is to tell fetch a way to retrieve your data like this:
$town_z = $stmt-fetch(PDO::FETCH_ASSOC);
although I think that it will get both result sets in case you don't define.
} else {
print $stmt-errorCode();
print_r($stmt-errorInfo());
}
Thanks in advance!
A better way debug that I know is using exceptions like:
try {
$sth = $conn-query($query);
$rs = $sth-fetch();
} catch (Exception $e) {
print failed :.$e-getMessage();
}
You can use try {} with almost everything so give it a try :-) .
Send us some feed back or post full source if you keep having trouble.
--
Thodoris
Re: [PHP-DB] multiple tables and correct select statement
In order to find out what is wrong with your query you could use
exceptions. A sample code could be:
try {
$query= ...
..your code
}
catch (Exception $e){
$errmsg = $e-getMessage();
print $errmsg;
}
This will print out the reason that mysql has for failing your code. An
obvious statement is that you should try to execute your queries using
the command line client or any other that you use before putting it in
use in order to find out what's wrong.
--
Thodoris
O/H Jas ??:
Hello all, I receive an error of the following: The used SELECT
statements have a different number of columns. Any help, pointers,
tutorials are appreciated.
Here are the two tables structure I am trying to pull from:
Table 1
mysql describe orders;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| id | int(255) | NO | PRI | | auto_increment |
| ordernum | int(10) | NO | | ||
| date | varchar(60) | NO | | ||
| time | varchar(20) | NO | | ||
| group | varchar(20) | NO | | ||
| purpose| varchar(255) | NO | | ||
| tracking | varchar(120) | NO | | ||
| contact| varchar(255) | NO | | ||
| eta| varchar(50) | NO | | ||
| department | varchar(125) | NO | | ||
| notes | varchar(255) | NO | | ||
++--+--+-+-++
11 rows in set (0.01 sec)
Table 2
mysql describe order_items;
+-+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-++
| id | int(11) | NO | PRI | | auto_increment |
| ordernum| int(124) | NO | | ||
| quantity| int(124) | NO | | ||
| description | varchar(124) | NO | | ||
| price | decimal(10,0) | NO | | ||
| partnum | varchar(255) | NO | | ||
| vendor | varchar(255) | NO | | ||
+-+---+--+-+-++
7 rows in set (0.00 sec)
And here is the statement I am using (PHP):
$query = ( SELECT * FROM `orders` WHERE ( `ordernum` LIKE $var\ OR
`purpose` LIKE \$var\ OR `tracking` LIKE \$var\ OR `contact` LIKE
\$var\ OR `date` LIKE \$var\ OR `time` LIKE \$var\ OR `eta` LIKE
\$var\ OR `department` LIKE \$var\ OR `notes` LIKE \$var\ ) AND
`group` = \$group\ ) UNION ( SELECT * FROM `order_items` WHERE (
`ordernum` LIKE \$var\ OR `price` LIKE \$var\ OR `partnum` LIKE
\$var\ OR `vendor` LIKE \$var\ OR `quantity` LIKE \$var\ OR
`description` LIKE \$var\ ) ORDER BY `ordernum` );
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Problems with character encoding
Hello list, I know that what I'm about to ask does not concern databases exclusively but please guys help. I am retrieving all the records from a mysql (myisam) table using PDO and I am trying to write it to an spreadsheet using php. Whenever I use English while writing into the spreadsheet everything works smoothly but when I use greek retrieved from the database nothing seems to do the job. I have used utf8_encode/utf8_encode, iconv and I have just compiled multi-byte encoding into php (mbstring) and used mb_convert_encoding() to convert the encoding but it still doesn't do the trick for me. Does anybody have previous experience concerning charset encoding in order to help me. Thanks in advance. PS: I am writing in sreadsheet format using Spreadsheet_Excel_Writer pear module* * -- Thodoris
Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??
Well the directory that houses the database should be writable.
Running the script from command line it gives you write access probably
but it won't work using mod_php because the web server probably can't
write. Try it and give us some feedback.
--
Thodoris
O/H Chris έγραψε:
Markus Wolff - NorthClick wrote:
Hey there,
I'm trying to open an SQLite3 database from a PHP very simple PHP
script:
$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo-query(SELECT * FROM page LIMIT 1);
echo Deleting pages\n; $pdo-query(DELETE FROM page);
echo Deleting websites\n;
$pdo-query(DELETE FROM website);
The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down version
of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).
When executed on the command line, this works perfectly. When I execute
the same script via Apache and mod_php, I'm getting this exception:
PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8
Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo-query() to $pdo-exec(), just to see what happens.
Well, what happens is that I'm getting a different error:
PDOException: SQLSTATE[HY000]: General error: 14 unable to open database
file in /home/mwolff/webs/markus/cms/test.php on line 6
Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried upgrading to the latest Debian package
of 5.2.4, to no avail).
It can't be file permissions, I've even tried to set the database file
to 777... no change at all.
My guess is still permissions. If you try a raw fopen instead of using
pdo, what happens?
?php
error_reporting(E_ALL);
ini_set('display_errors', true);
$fp = fopen(dirname(__FILE__).'/frontend.db', 'r');
if ($fp) {
echo Opened dbbr/\n;
} else {
echo Unable to open dbbr/\n;
}
fclose($fp);
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??
I run into this:
http://www.php.net/manual/en/ref.pdo-sqlite.php
while searching for a solution to this exception:
PDOException: SQLSTATE[HY000]: General error: 1
Take a look at it bacause this might be happenning due to version
incompatibilities. For example only sqlite version 3.1.4 and after can
read file formats 1,2 and 3. Is you database format new?|
|
--
Thodoris
O/H Markus Wolff έγραψε:
Chris schrieb:
It can't be file permissions, I've even tried to set the database file
to 777... no change at all.
My guess is still permissions. If you try a raw fopen instead of
using pdo, what happens?
?php
error_reporting(E_ALL);
ini_set('display_errors', true);
$fp = fopen(dirname(__FILE__).'/frontend.db', 'r');
if ($fp) {
echo Opened dbbr/\n;
} else {
echo Unable to open dbbr/\n;
}
fclose($fp);
?
Hey Chris,
mmh wonder what could possibly be wrong when even trying 777? :-)
Anyway, just to make sure (and because I'm desperate enough to grasp for
straws right now), I tried exacly what you proposed and it still yields
the same results.
Thanks for your suggestion anyway, sometimes one forgets to try the most
obvious things first :-)
CU
Markus
[PHP-DB] Build a form that returns to the parent window
Hi guys, I have been working on a project for some time now and I'm trying to make a form that includes (many things and) an edit box with a button by it. I want this button to open a pop-up window and include a search form that queries a database and return to its self the result set in a table in which the last cell contains a link (or button). This link should return to the parent window a value of this specific row. Well I have build the whole interface and since php is server side I thought I could write this including javascript. Non of my experiments worked because the pop-up window does not returm to the parent window something. I searched through the web and nothing seems to do the magic for me. Please any suggestions would be welcome cause I have been working on this for several hours as we speak. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??
Hi again Markus,
This is realy confusing but since the command line execution works
for you then the problem should be php configuration for the mod_php. I
came up with this try to edit your php.ini and in case you use
pdo_sqlite extension then change the order of the loaded modules to this:
| extension=pdo.so
extension=pdo_sqlite.so
extension=sqlite.so
You could also define the full path of the extensions like this:
||
extension_dir=/usr/local/include/php/ext/pdo/
instead of this:
||extension_dir = ./
In case your apache is compiled by hand and you have updated sqlite you
should recompile your apache against the new sqlite.
Hope this do some magic for you cause I have run out of ideas.
|
--
Thodoris
O/H Markus Wolff έγραψε:
Hi Thodoris,
I've checked on one of the three boxes now and the SQLite version used
by both the commandline client and PDO is 3.2.8. I know the other two
boxes have different versions, but I always created the database anew
on each box.
I also tried chown/chmod on the parent directory, no change.
CU
Markus
Thodoris schrieb:
Hey Markus,
You should try to chown apache:apache and chmod +w to the
directory that includes frontend.db . And the link that I posted says:
|/*
** file_format==1Version 3.0.0.
** file_format==2Version 3.1.3.
** file_format==3Version 3.1.4.
**
** Version 3.0 can only use files with file_format==1. Version 3.1.3
** can read and write files with file_format==1 or file_format==2.
** Version 3.1.4 can read and write file formats 1, 2 and 3.
*/
Meaning that not all sqlite3 versions support all file formats. That
is why you should check the version of sqlite.
|
--
Thodoris
O/H Markus Wolff έγραψε:
Hey there,
I've double-checked on three different machines now, and I'm always
getting the same error. All having different versions of PHP,
Apache, PDO and SQLite. So I figure it must be something that I'm
doing wrong. I just can't figure out what it is - and I'm puzzled
because I had used SQLite before (although briefly) and don't think
I'm doing anything different than before.
Anyway, here's what I'm doing, step-by-step:
# sqlite3 frontend.db
Here I insert the following SQL script:
CREATE TABLE website (
website_id INTEGER PRIMARY KEY,
always_expand INTEGER
);
CREATE TABLE page (
page_id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER,
website_id INTEGER NOT NULL,
title TEXT,
link TEXT,
depth INTEGER,
visible INTEGER,
element_id INTEGER,
nav_path TEXT,
protected INTEGER,
sort_order INTEGER
);
Then I exit the client and make the PHP script:
# nano test.php
The content of the script still being that of my original message.
Then I adjust the rights:
# chown apache:apache frontend.db
# chmod 777 frontend.db
Then I execute the script on the command line:
# php test.php
No error.
Then I call the script on the website, one of the examples being:
http://www.21st.de/test.php
The script still manages to open the database and do a SELECT query,
but throws the said exception when trying to do the DELETE statement.
These are all the steps that are involved to reproduce the error on
three machines. No more, no less. Now, have I overlooked anything?
Am I missing something really, really stupid? Or is it some kind of
a bug? But certainly that could not have gone unnoticed for so long?
(Tested on PHP versions 5.1.4, 5.2.0 and 5.2.4).
CU
Markus
Markus Wolff - NorthClick schrieb:
Hey there,
I'm trying to open an SQLite3 database from a PHP very simple PHP
script:
$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo-query(SELECT * FROM page LIMIT 1);
echo Deleting pages\n; $pdo-query(DELETE FROM page);
echo Deleting websites\n;
$pdo-query(DELETE FROM website);
The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down
version
of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).
When executed on the command line, this works perfectly. When I
execute
the same script via Apache and mod_php, I'm getting this exception:
PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8
Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo-query() to $pdo-exec(), just to see what
happens.
Well, what happens is that I'm getting a different error:
PDOException: SQLSTATE[HY000]: General error: 14 unable to open
database
file in /home/mwolff/webs/markus/cms/test.php on line 6
Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried upgrading to the latest
