Re: [PHP-DB] mysqli prepared statement query result sets
Hi Gerard, Thanks for the reply. I'm writing a db abstraction layer driver for MySQLi. I'm glad to finally get a chance to play around with these new functions, but am completely stumped by this question: Is there no way to get back a standard resultset when using prepared statement queries? snip I don't see the solution, but I hope I'm just missing something because I've been staring at it too long. No you're not missing anything... I ran into the same thing... http://marc.theaimsgroup.com/?l=php-dbm=109625996830773w=2 So I ended up simulating prepared statements via php itself. The way the mysqli extension is currently setup, is that you can either use the normal functions by themselves, or you use the statement functions by themselves. They cannot be used together, which I think Never mind, I'll keep my thoughts to myself... Ooops, I guess I should have searched the list itself; I did some google searches to no avail. This is really unfortunate. This API sucks! I was looking forward to being able to use native prepared statements rather than emulating, but I guess that's really not feasible for this project. Argh. Thanks again for response. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysqli prepared statement query result sets
Hi Gerard, I believe he is talking about - http://dev.mysql.com/doc/mysql/en/SQLPS.html But IMHO, I think emulating via php would be faster, as that is alot of talking to a database to get the job done, especially if the database is on a remote host. Ahh, ok. Yes, this is interesting, but certainly not ideal from a uniform API perspective. I will stick w/ emulation in PHP. Granted, he is correct. We have no right to say that is sucks. Me personally, I do not know at whom to point a finger to blame (maybe the mysql C API), but it is what it is unfortunately. We just have to make the best of it... Yes, you're right. Saying the API sucks was certainly an exaggeration. It is a disappointment, but no one sucks. Georg did an awesome job bringing this to PHP5. I'll stop complaining just stick w/ classic mysql API ;) -Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysqli prepared statement query result sets
Hi, I'm writing a db abstraction layer driver for MySQLi. I'm glad to finally get a chance to play around with these new functions, but am completely stumped by this question: Is there no way to get back a standard resultset when using prepared statement queries? I can't believe this would be the case, but it seems that the only option when using prepared statements is to call myslqi_stmt_bind_result(), binding results to php variables and then call mysqli_stmt_fetch($stmt) until it returns null. i.e. $sql = SELECT name, age FROM friends WHERE country = ?; $stmt = mysqli_prepare($link, $sql); $country = Haiti; mysqli_bind_param($stmt, s, $country); mysqli_stmt_execute($stmt); /* bind result variables */ mysqli_stmt_bind_result($stmt, $name, $age); /* fetch values */ while (mysqli_stmt_fetch($stmt)) { printf (%s (%s)\n, $name, $code); } What I want to be able to do is use things like mysqli_fetch_assoc() instead of this weird, side-effect-prone mysqli_stmt_fetch() to retrieve the results. I don't see the solution, but I hope I'm just missing something because I've been staring at it too long. Thanks in advance, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Which Database Abstraction Layer ?
Hi John, John Lim wrote: /abstract/ stuff. For example, ADOdb would completely fail to be portable accross databases where the case of the column names in result array changes (e.g. postgres always returns lowercase col names, Oracle always uppercase, MySQL returns mixed case, SQLite is configurable). This is one example of why some layers (like PEAR::[M]DB) may be slower. Hans, This is incorrect. You can configure the case of the column names in ADOdb. See http://phplens.com/adodb/reference.constants.adodb_assoc_case.html Regards, John Sorry about that. I certainly don't want to spread disinformation (!) Is this true for all the drivers ... ? E.g. I looked at the postgres64 driver and saw no case-changing code in the MoveNext() method; I didn't look at the other drivers after that, though. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Which Database Abstraction Layer ?
Lester Caine wrote: snip So you start with ADOdb datadict and build the database from that - works well when adding any supported engine. Reserved words which differ between engines are another problem area though. Yeah, but not just reserved words -- but even what is allowable in a query. MySQL is notorious, of course, for not only the f'd up types (ENUM, proprietary TIMESTAMP format) but also for being rather lax about using aggregate functions w/o specifying columns in GROUP BY clause, etc. There's other behavior that differs too: e.g. in MySQL (and others) LIKE performs case-insensitive search. In PostgreSQL we have instead ILIKE for insensitive searching and LIKE for case-sensitive searching. Of course when you bring Oracle into the picture the differences are compounded many fold again (and at that point I no longer can keep up). I would also suggest you also add Creole (http://creole.phpdb.org) to your test list if you are considering abstraction layers for PHP5. Doesn't do Firebird yet ;) - but it looks interesting. As long as it has not made the mistake of using MySQL as the SQL standard. Many other packages are simply MySQL wrappers with cobbled support for a couple of other engines. No, that's true; no Firebird yet. Drivers needed :) It's basically a slightly modified version of the JDBC API for PHP. It does not use MySQL as an authority on SQL ;) I use it primarily with PostgreSQL -- and to a lesser extent SQLite and MySQL. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Which Database Abstraction Layer ?
Jean-Philippe Côtê wrote: Quoting Lester Caine [EMAIL PROTECTED]: I am sure if I started again now I would probably be using something other than ADOdb. But at the time it fitted the bill and where there were problems they were quickly cleared. Unlike some of the other options I tried. May I ask which other ones you would consider ? It is becoming obvious that I will have to do some benchmarking myself. Therefore, if you had other worthy contenders to suggest (besides ADOdb and PEAR::MDB) I would probably include them in my tests. You definitely should do your own benchmarks. Bear in mind that the ADOdb benchmarks test a certain type of behavior -- namely repeated select statements. Also bear in mind that the speeds of the different layers are going to be inversely proportional to how well they actually /abstract/ stuff. For example, ADOdb would completely fail to be portable accross databases where the case of the column names in result array changes (e.g. postgres always returns lowercase col names, Oracle always uppercase, MySQL returns mixed case, SQLite is configurable). This is one example of why some layers (like PEAR::[M]DB) may be slower. Database abstraction is a really tricky thing. None of these layers provide 100% abstraction; that can only really be achieved with a DAO/object persistence layer (e.g. see DB_DataObject in PEAR, or Propel http://propel.phpdb.org). I would also suggest you also add Creole (http://creole.phpdb.org) to your test list if you are considering abstraction layers for PHP5. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Tricky MySQL / php Script
Hi Vern, Vern wrote: The first recordset of thumbnails is retrieved by a URL link ($HTTP_GET_VARS['id']) which is the user's ID number. Click on one of the thumbnails a new page is open that display the larger photo, but only that photo. I can of course pass along the user's ID as well mypage.php?photoID=uploads/1052367746.jpguserID=215 which of course will allow me to create a new recordset based on the user's id SELECT * FROM penpals_photos WHERE penpals_photos.filename = $photoID but how do I point the database first record at the photoID so that I can use the next and previous options to see the next/previous picture? What you are doing makes sense, but there are some other questions that must be answer. 1) How are photos and users related? i.e. what type of relationship (1-n, n:m, etc.), what tables / columns ... ? 2) What makes a thumbnail previous or next? i.e. are they sequenced by dates or some arbitrary sequence field, or ... ? If the photos and users are related by some intermediary table, you could probably do a query like this to get all of the photos for a user: SELECT photos.* FROM penpals_photos photos, penpals_photos_x_users photos_users WHERE photos.photoID = photos_users.photoID AND photos_users.userID = $userID Just add the ORDER BY to order them correctly. To get the actual prev or next given a particular photo id, you can just use and and the sequence column DESC/ASC order by and LIMIT 1 I'll leave that to you to figure out; it's not too tricky once you've got your queries to fetch all of the photos for a user in the correct order. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Prepared statements via mssql extension
Gerard Samuel wrote: Is it possible? Just checking... It's not unless you use stored procedures. It'd be nice ... :) Stored procedures via mssql_init(), mssql_bind() and mssql_execute() work quite well, though. Read the user comments in the manual as they will help you get over some common gotchas. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: access denied
Water_foul wrote: i get this error: Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in C:\Inetpub\localroot\aichlmayr.net\sites\aaron\module\runescape\runerunner\s ervices.php on line 3 and the code up to line 3 is: ?php //Database Querys $connection=Mysql_connect($dbHost , $dbName , $dbPassword); i have included this in another script that sets $dbHost, $dbName and, $dbPassword to the correct things to connect to the db Did you actually confirm that you are able to connect using that host/user/password from the command line? I mean, the error is pretty frickin' explicit ... check your database credentials. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Stopped working?
James Hatridge wrote: HI all... I just upgraded my SuSE system from 8.2 to 9.1 now the counters on my web sites are not working right. I tracked it down to this line: $cur_ip = $REMOTE_ADDR; I believe that this has stopped working. The idea was my counter kept your number so that you were only counted once. But now it's not getting the number. Has this command changed in the past 2 SuSE version? This appears to be another case or register_globals issue. Please do search list archive before posting, as I seem to remember an almost identical email in past couple weeks. By default in PHP register_globals setting (in php.ini file, not sure where that is on SuSE) is now Off. Turn it back On to make your script work again. Furthermore, when upgrading your PHP version (inherent in performing a huge SuSE upgrade), read the changelog to see what's actually gonna break in your sciprts. I'd wager that other things are gonna break too (e.g. magic_quotes_gpc). Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Beta Testers Needed
Yeah, this is a joke, right? You want people to intall your software on their webservers which will send information about their servers accross the network to your server? Can we just cut to the chase I'll send you the root password on my severs? Anyone interested in being alerted when their servers are down can use one of the many, many existing open-source solutions (which do a heck of a lot more than monitor uptime apache). Hans Kenny wrote: Sorry all The e-mail address is [EMAIL PROTECTED] -Original Message- From: Kenny [mailto:[EMAIL PROTECTED] Sent: 15 June 2004 11:44 To: DBList Subject: [PHP-DB] Beta Testers Needed Hi All, I am currently looking for beta testers to test my server monitoring software, Brief synopsis of what it entails, Install a small (2.92 k) file on your web server Add either a new db or use and existing db this will only be 1 table with minimal information just basically holding your Key What I will monitor Server Load averages Server Uptime HTTP PHP MySQL If any of these services go down then I will automatically send you an alert to your e-mail address, (For the testing phases we will not be sending SMS alerts) I will generate graphs indicating load averages and server outages I will send daily reports telling you how the web server is performing. The testing phase will last approx 1 month but you can terminate at any time if you wish to There is no payment involved either from my side or yours. We are initially looking for 50 testers but this could increase in the future, Please only *nix servers for now running Apache / MySQL / PHP Please send your interest off the list to [EMAIL PROTECTED] and I will send you full instructions on how we would like to proceed and when the testing will start Thanks Kenny -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Beta Testers Needed
Hi Kenny, I'm not trying to trash, what might very well be a great product. I'm just very wary (as everyone should be) about communicating private server data to some remote host. Even things that seem trivial could probably be used in creative ways. I'm a litlte paranoid, but not without reason :) Making it open-source is certainly a good start. When I say software all it is, is a small script the generates a xml file, most of the Open source software that does this type of monitoring only pings your ports so there is no way of actually telling if the service is really running, Yes, that's true. But Nagios, for instance, uses the model you are suggesting -- it has a daemon on the inside that communicates w/ master server. Your script may be more secure than Nagios, but seems to be operating on exactly the same principle. The big difference, however, is that w/ Nagios deployments you are not trusting some 3rd party server to your data. Please feel free to view the parsed file at www.xarex.com/monitor/client.php If you find any security flaws please let me know so that I can block them, this is one of the main reasons for having a beta test I'll definitely check it out. Again, perhaps my initial reaction was a little strong :) and server monitoring software is good. BUT ... You are asking for a lot if you want people to entrust information about their servers that is not public (otherwise you wouldn't need to install anything) to an unknown server. Even to a trusted server; (think of all the uprise against MS Passport, which arguably stores rather benign information). Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql not receiving the data
Hi Andrew, Andrew Rothwell wrote: Thank you everybody that responded so quickly - I used the suggestion of Franciccio - and the data is now gow into the db Thank you very much - I really appreciate the help. Another question - with this fix in place - do I still need the register_globals = On ? Or can I now turn it off? It seems like you should have kept your old php.ini file, as this other error you encountered was probably due to your old php.ini file having this setting: magic_quotes_gpc = 1 That INI var instructs PHP to automatically addslashes() to any GET/POST/COOKIE data. I would suggest turning this back on, unless you've thoroughly redesigned your code to not need it. This is unrelated to register_globals, which you will need to leave on unless you redesign your application. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Anyone using MSQL Server with PHP?
Hi Miguel, I would also strongly suggest that you use a salted hash if you are concerned with security -- especially if that md5 could ever be compromised or pass in clear text over an internet connection. The md5() function in PHP produces unsalted hashes, which are fairly easy to crack. Unlike md5(), the PHP crypt() function will generate salted hashes. On my windows system it creates salted MD5 hashes (other operating systems may have other options like SHA1). A salt is essentially an additional random element that gets added into the password hash. This means that unlike md5() you will get a different hash each time you run crypt() on an identical string: crypt(hello) = $1$bJoW4DmS$GDNwsRSjd5rwkfra6KOh10 crypt(hello) = $1$DnL7LQXm$eioj87M92X3IQvoTEquY21 crypt(hello) = $1$h488/RAa$e8tA4K1hEuBBRnagJbBnV1 instead of: md5(hello) = 5d41402abc4b2a76b9719d911017c592 md5(hello) = 5d41402abc4b2a76b9719d911017c592 md5(hello) = 5d41402abc4b2a76b9719d911017c592 Why does this matter? Well, because of something called the time-memory tradeoff (try a google search to see the math behind this). The basic principle of the time-memory tradeoff is that you could either spend a long time trying to brute force each password hash or you could just spend time once creating really big (memory) tables that contain all the possibilities and then crack a hashed password in seconds. Of course the more complex the passwords you want to crack the longer it takes to build these tables (could be days, weeks, months, etc.), but the idea is that once you've built these tables it only takes a few seconds to crack any supported password. Time-memory tradeoff only works with unsalted passwords because these are 100% predicatble (hash of unsalted MD5 is always the same, as seen above). To check an entered password against the original when using salted hashes, you need to do something a little different: you have to pass the original encrypted password as the salt to the crypt() function (the crypt() function extracts the salt from the passed password and uses that same salt to encrypt the entered password). For example in PHP, check entered password against real password like this: if (crypt($entered_pw, $real_pw) == $real_pw) { // login success } Hope that helps. (Anyone, please correct any errors or misinformation above!) Hans Miguel Guirao wrote: Thanks!! It Works out pretty nice!! Miguel Guirao Servicios Datacard www.SIASA.com.mx -Mensaje original- De: Justin Patrin [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 10 de Junio de 2004 05:51 p.m. Para: [EMAIL PROTECTED] Asunto: [PHP-DB] Re: Anyone using MSQL Server with PHP? Miguel Guirao wrote: Hi!! Anybody here using PHP with SQL Server? I would like to use a similar function to password () from MySQL under SQL Server. Anybody knows of a similar function under SQL Server? Kind Regards, Miguel Guirao Servicios Datacard www.SIASA.com.mx If it's for your app only, you could use md5() in PHP. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: mysql adding
Steven Morgan wrote: is there anyway to add 1 to an int with 1 mysql query, i know i can pull the value down with a SELECT then add 1 with php.. then do an UPDATE on it.. but i didn't know if there was any other way? Do you mean this: UPDATE tablename SET intcol = intcol + 1 ? Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: How to identify primary key
Hi, Scot L. Harris wrote: Using PHP 4.2.2 and postgresql 7.2.4. Trying to find a way to identify the primary key of a table given just the tables name. I don't have a quick answer but can point you to where you can hopefully find what you are looking for. Try looking at the queries in this class: http://creole.tigris.org/source/browse/creole/creole/classes/creole/drivers/pgsql/metadata/PgSQLTableInfo.php Creole is a PHP5 db abstraction layer that, among other things, has methods to get primary key info from db metadata. I think the answer to your question (and possibly others about pgsql metadta) will be present in that class. I had some help in getting that to work correctly. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Load balancing and SQLite
Hi all, I think Justin's absolutely right: don't use SQLite if you're doing the type of project where you need load balancing. As for NFS, I haven't had major problems with it myself, but it's always called the Network Failure System by many people I trust. It can (will) also cause quite a bit of extra network traffic, especially since your database engine will reside on a different machine than the database file, likely causing reading of large quantities of data over the network that just get thrown out by sqlite engine. ALso ... I don't know about SQLite specifically, but I wanted to mention that using NFS w/ these database systems can lead to trouble. Notably, you *cannot* use NFS w/ BerkeleyDB databases on account account of physical drive requirements -- e.g. ability to map drive sectors into RAM, etc. I don't know if SQLite is similar to BerkeleyDB, but as they are both embedded db systems I wanted to point that out. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Secure MySQL Access md5()
Hi, Passwords in the mysql.users table must be encrypted using MySQL's native PASSWORD() function, not MD5(). With newer versions of MySQL, I think there is some change in this, but AFAIK for 4.0.18 this is still true. When connecting to the database you always use the plaintext password, and MySQL will do the encrypting (using PASSWORD()) and check it against what is in the row of the users table. HTH, Hans Gary Theisen wrote: Hi all, I've got: WinNT php 4.3.1 phpmyadmin 2.5.6 MySQL 4.0.18-nt I can connect to my db via my php script using: [php] $connection = mysql_connect (localhost, root, ) [/php] That works no problem. This will not work however: [php] $somePass = md5(somePass); $connection = mysql_connect (localhost, someID, $somePass) [/php] gives me this error: [quote] Access denied for user: '[EMAIL PROTECTED]' mailto:'[EMAIL PROTECTED]' (Using password: YES) [/quote] I set someID up in the db using phpmyadmin, with it's password using the md5() function. I can see via phpmyadmin that someID does indeed have a 32 byte encrypted password stored. I can then compare the stored md5 password to the md5 password I'm passing to try to connect...via $somePass. The encrypted passwords match exactly. Why wouldn't the match be confirmed...allowing me to connect? Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] lamer noob with repeat question
Hi, I think there are probably a few reasons why no one has answered: 1) This isn't a database problem, 2) You can apply some pretty standard debugging practices to narrow down the problem, and 3) Your code is hard to follow -- and fact application logic is being lost in the all the escaped HTML that you are echo()ing. so, here's what I'd suggest: -- Start by echoing your $_REQUEST array at the top of your script: print pre; print_r($_REQUEST); print /pre; Make sure your 'action' var is being set correctly, etc. This is standard debugging stuff. Print values all over the place; add things like: print Got this far: . __LINE__ . br/; so that you know where your code is dying. -- Remove any @ error suprression ... until you know your script works you shouldn't be silencing errors (Especially as in some cases these errors could be fatal causing your script to terminate with that infamous white screen). -- Use the switch() statement to make your logic easier. DON'T USE ARBITRARY NUMBER VALUES FOR YOUR ACTION SWITCH! And consider separating out your HTML markup from your application logic. You don't have to do anything fancy like use a template engine; just include a PHP file that is essentially just HTML with embedded ?php ? tags to echo values. (no logic in that file, just flat HTML). Consider having two switch statements -- $action and $view. The first one handles things like 'save', 'load', etc. Based on the result of actions (like inserting to db, etc.) of the first switch you can change the view that should be displayed. This is fairly simple and will go a long, long way to making your code easier to debug -- and easier for other people to read. Cheers, Hans Dan Bowkley wrote: Anyone? - Original Message - From: Dan Bowkley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, May 02, 2004 1:21 AM Subject: [PHP-DB] lamer noob with repeat question Hello everyone, I've been working on (read:tearing my hair out over) my mom's website for some time now. Specifically, I'm trying to get her work order database up and running. The basic idea is this: you start out adding a new record by going to add.php. It sees that you've not done anything yet and thus presents you with a form to fill out. That form gets submitted to add.php, which sees that you're adding something. It checks for a duplicate work order number (and eventually other errors) and then either adds the stuff you submitted into the DB, or pops an error and presents the form again. Alas, it does nothing. When you initially load the page, it works okay, sensing that you've not yet done anything and displaying the form. But when you submit data, it spits out naught more than a blank page, and doesn't add anything to the database. Damned lazy script. What I've got so far is this: html headtitleThe Board Lady - Work Order Database 0.1a/title/head body ?php define ('DB_USER', 'user'); define ('DB_PASSWORD', ''); define ('DB_HOST', 'localhost'); define ('DB_NAME', 'boardlady'); $dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to database: ' . mysql_error()); @mysql_select_db (DB_NAME) OR die ('Could not connect to database: ' . mysql_error()); $page_req=$HTTP_GET_VARS['action']; if ($page_req == ) {$page_req=0;} if ($page_req == 0) { echo SWORD data entrybr\n; echo form action=\add.php\ method=\get\; echo Work Order #: input type=\text\ name=\wo_num\br\n; echo Customer Name: input type=\text\ name=\name\ Phone: input type=\text\ name=\phone\br\n; echo Email Addy: input type=\text\ name=\email\ Date In: input type=\text\ name=\date\br\n; echo Board Type and SN: input type=\text\ name=\board_type\ Last 3 of SN: input type=\text\ name=\last_three\br\n; echo Weight In: input type=\text\ name=\weight_in\ Weight Out: input type=\text\ name=\weight_out\br\n; echo input type=\hidden\ name=\action\ value=\1\\n; echo INPUT type=\submit\ value=\Add Work Order\ INPUT type=\reset\br\n; } if ($page_req == 1) { $wo_num=$HTTP_GET_VARS['wo_num']; $name=$HTTP_GET_VARS['name']; $phone=$HTTP_GET_VARS['phone']; $email=$HTTP_GET_VARS['email']; $date=$HTTP_GET_VARS['date']; $board_type=$HTTP_GET_VARS['board_type']; $last_three=$HTTP_GET_VARS['last_three']; $weight_in=$HTTP_GET_VARS['weight_in']; $weight_out=$HTTP_GET_VARS['weight_out']; $query_testingforadupe = SELECT job_no FROM boards WHERE job_no == $job_no ORDER BY job_no ASC; $result_testingforadupe = @mysql_query ($query_testingforadupe); if ($result_testingforadupe) { echo That's a duplicate work order number, you ditz. Try again, this time without screwing it all up.brbr\n; echo form action=\add.php\ method=\get\; echo Work Order #: input type=\text\ name=\wo_num\br\n; echo Customer Name: input type=\text\ name=\name\ Phone: input type=\text\ name=\phone\br\n; echo Email Addy: input type=\text\ name=\email\ Date In: input type=\text\ name=\date\br\n; echo Board Type and SN: input type=\text\ name=\board_type\
Re: [PHP-DB] lamer noob with repeat question
Dan Bowkley wrote: } else {echo OOPS! Your programmer is an idiot!\n;} }} ... and consider removing this for a couple reasons: 1) If a user should never see this, then design your application so that they never do. 2) If this code ever could be executed, then you probably want to handle it in a way that would stand less chance of scaring away future business. :) Good luck! Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Image / file uploader
Craig Hoffman wrote: This is really becoming an irritating small bug. I tried the curly brackets and the photo name still does not show up when I echo out the query. Everything else about the script works fine. Why does PHP choke on forms that submit files to themselves? I am slowly running out things to try... Suggestions? Remove the action attribute of your form tag. Default is to post back to same page. echo (form method='post' action='{$_SERVER['PHP_SELF']}' encType='multipart/form-data' Also yuk! Have you considered at the very least separating your presentation layer out into separate files so that you don't echo() your HTML? This will make your life easier if ever you want some non-PHP person to help w/ layout. It'll also make your life easier if you want to redesign the form later w/o having to muck around in your processing logic or if you want to add caching to your site. It'll also make your life easier if you want to move templates out of the web root or want to change your app design to use an object-oriented application framework (like Mojavi, Binarycloud, etc.). etc., etc., etc. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] inserting same data into multiple tables question (?)
Hi, John W. Holmes wrote: From: JeRRy [EMAIL PROTECTED] I want to input the same data into multiple tables in one query if possible. It's not. You really have to question your database schema if you need to do this. Yeah, I agree w/ John -- you probably want to examine your database schema if you think you need to do that. If you are building a really complex application that really does require a whole bunch of inserts for adding a user, then you should probably consider using TRIGGERS -- and a database that supports them (e.g. PostgreSQL). http://www.postgresql.org/docs/7.4/interactive/plpgsql-trigger.html You would do something like (in Postgres): CREATE FUNCTION do_other_inserts RETURNS trigger AS ' BEGIN INSERT INTO table2 (username) VALUES (NEW.username); INSERT INTO table3 (username) VALEUS (NEW.username); END; ' LANGUAGE plpgsql; CREATE TRIGGER other_inserts AFTER INSERT ON table1 FOR EACH ROW EXECUTE PROCEDURE do_other_inserts(); Of course you'll probably want to also add triggers to handle deleting and updating users. The better/faster solution would be to use integer (user_id rather than username) foreign keys and specify ON UPDATE CASCADE and ON DELETE CASCADE so that the updates and deletes would propogate automatically. You'd probably still need an INSERT trigger, though, if you have related rows which must be added. I'm assuming that since you asked whether you can INSERT into multiple tables, that this is probably sounding a little overwhelming. I encourage you to spend some time to figure this stuff out now, though, because it will only become a nightmare when you actually have production data you realize that your data model is inefficient unscalable. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Image / file uploader
Here you go: I mostly echo out the HTML. I have included the entire form here. Thanks - CH echo (form method='post' action='{$_SERVER['PHP_SELF']}' encType='multipart/form-data'); echo (table border='0' cellpadding='5' width='500' ... If you could send the actual resulting HTML that is echoed by your script, that would be more helpful in diagnosing why your browser isn't posting back to the right page. HL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: From mysql to mssql...
Hi Tristan, Tristan Pretty wrote: However, I've been given a one page project to do, that needs to add the contecnt of a form to an mssql database... I'm fine with doing all the page, apart from talking to mssql... how drasitally does this differ from the standard mysql functions tha tI use..? The quick answer is that you're in luck: the MSSQL API looks a lot like MySQL's PHP API. For example, mysql_fetch_array() - mssql_fetch_array(). A practical word of advice, though: use a database abstraction layer. There are a number out there that will make your life easier: PEAR::DB, PEAR::MDB, ADOdb, Creole (PHP5). Most (all?) of these support MS SQL Server and they will all make thing much easier -- especially if you ever decide to try to run that SQL Server app on MySQL. I would recommend MDB (or MDB2) if you are using PHP4; ADOdb is fast, but if you care about source code quality and/or design you'll use PEAR. One difference you'll probably discover, if you extend your app at all, is that there is no LIMIT in MS SQL Server. To do paged queries is quite difficult -- and that's why using a db abstraction layer will help you out. Other differences in the SQL / results are quite numerous. Date/time formats look different (some abstraction layers will help with this). MySQL also allows for some pretty sloppy SQL, and MS SQL Server will complain when you try to do things like add aggegate functions to your SELECT() clause without having a GROUP BY clause. (Also, I nearly ahd to do it in asp... I'd never used it before, but after lookign at it, it made me damned happy that I use PHP.. way more user friendly!!) At the risk of being stoned, I will say that I've had the opportunity to work with ASP.NET/C# and I absolutely love that famework (and the C# language). It's a very well conceived answer to problem of OO n-tier web application development. I love PHP and have been using it for many years, but I also love OO app architecture and even PHP5 really doesn't play in the same league as solutions like ASP.NET. Classic ASP is a different story. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: PostgreSQL lib and character case
Hi - Tumurbaatar S. wrote: I use pg_fetch_array() to get a record content. But it seems that to access elements of the returned associative array, I should use lowercase field names. Is there any way to use case-insensitive field names? This is how Postgres works: it always returns arrays indexed with lowercase results. AFAIK there's no way to change this behavior from PHP. In general, it's important to know that different databases do this differently: - MySQL will return case matching the case of column names in the db -- or if you specify column names in your select clause (SELECT myColUmnName FROM ...) then the case of the array will match the case you use in your SELECT clause. - Oracle will return all UPPERCASE column names. - SQLite is configurable (defaults to mixed case) - PostgreSQL returns all lowercase ... etc. Of course, as someone mentioned, you can always col strtolower() when trying to access a column from postgres result set: $arr = pg_fetch_array($q); $value = $arr[ strtolower($mixedCaseColName) ]; It's best practice to use a database abstraction layer that provides column name case changing portability features -- like PEAR::DB or Creole. That way you can always use a single case (e.g. lowercase) for accessing columns and you won't have to rewrite all your code when you try to deploy your app on Oracle. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Session
Hi - John W. Holmes wrote: The session does not exist past the point of closing the browser unless you increase the lifetime of the session cookie itself. I would recommend you just leave it at zero, though, meaning it only persists for as long as the browser window is open. The longer you make the sessions last, the easier it is for someone to hijack them. One point of clarification here. The *cookie* doesn't exist past the point of closing the browser, but the session file (assuming using files) will continue to exist until it has been garbage collected or until session_destroy() is called. Also, if you weren't using cookies (were passing session id in URL) you could open your browser again and navigate back using your history you'd still be logged in. That's why it's a good idea to always call session_destroy() when a user logs out. That will effectively delete all session data on the server. Of course, as John mentions, once the user closes the browser the in-memory cookie will be deleted and (if you're using only cookies) there's no longer any connection between that client computer and the session stored on the server. In that respect the session exists no longer, but as mentioned the data will still be there on the server. If someone knows (e.g. hijacker) the session ID, they can revive the session by just adding it to the URL. On that note, here are a few things you might want to consider if you want to make sessions more secure: - use only cookies for sessions. (session.use_only_cookies = 1) This prevents the session ID from *ever* being added to the URL. URLs get logged -- by apache, by proxy servers, by user bookmarks :) -- and if a URL contains a session ID then you have that mentioned problem where a session can be easily revived after the user closes the browser (effectively session hijacking, intentional or not). - regenerate the session id when a user logs in. simply run session_regenerate_id() after the username/password has been verified. This goes a long way to prevent session fixation, another type of session attack in which an attacker makes a user log in using a fixed session id (e.g. by clicking on a link that includes something like PHPSESSID=1234); once the user logs in using this fake session id, the attacker can use that session id in order to have access to the system as whichever user logged in. (Do a search on session fixation for more information on that.) - keep your gc_maxlifetime as small as possible; that way if a user does close their browser their session won't remain active for 12+ hours. You might want to consider ways of periodically refreshing the page using an iframe or even just a meta refresh... solution. That will address the need to stay logged-in while the browser is open, while also allowing you to have a very brief session lifetime. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] [ANNOUNCEMENT] Propel 1.0-alpha1 released
After a long period of quite intense development, the Propel team is pleased to announce the release of Propel 1.0.0-alpha1. Propel is an object persistence layer for PHP5 based on Apache Torque. In practical terms, Propel allows you to use objects instead of SQL to read and write rows in your database tables. Propel provides (1) a generator that creates SQL definition files (DDL) and PHP classes for your datamodel, and (2) a runtime engine that transparently handles object persistence and retrieval. Propel makes it easy to customize generated classes; additionally, the use of XML, PHP classes, and the Phing build tool make it easy to integrate Propel with an existing application framework. Visit http://propel.phpdb.org to learn more about Propel. Some of Propel's features include: * Use of simple XML schema for datamodel definition * Generation of PEAR-style compliant, phpdoc commented, PHP5 classes. * Easy to customize object model ( changes won't get overwritten). * Generation of SQL definition files (DDL) for your RDBMS * Several popular databases supported: MSSQL, MySQL, PostgreSQL, SQLite. * Support for reverse-engineering XML schema from database * Use Criteria OO query system instead of writing SQL queries * Generated methods to easily efficiently work with (foreign key) relationships We're excited to finally have a PHP5 stable enough for a release. We hope that Propel will make it easier than ever to develop object-oriented, database-driven applications in PHP5. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL category tree db sorting
Yes, in briefly reading your requirements, I believe the answer you want is the nested set model: Here's an article that looks right ... (quick google search) http://www.dbazine.com/tropashko4.shtml More information on this is contained in the SQL for Smarties book (which is an excellent book) by Joe Celko. There are some other online articles dealing with nested set -- try google. *Also* to make your life much easier you might want to have a look at the PEAR DB_NestedSet package. This makes the nested set queries much simpler. Cheers, Hans Age Bosma wrote: I'm trying to work out what the most efficient way will be to get the complete tree structure from top to bottom of a category tree db. Starting with the first main branch listing it's first child branch followed by it's children, after that the second child branch, ect. When the first main branch is done it should start with the second main branch, etc. I've got a category tree db with the following columns: cat_id - Unique id for each row parent_id - cat_id of its parent, 0 if it has none prev_sibling_id - cat_id of it's previous sibling, 0 if it has none next_sibling_id - cat_id of it's next sibling, 0 if it has none. To get the complete tree stucture from the db, is it possible to get the sequence from top to bottom by one sql query, should the whole table be gotten and sorted using php or does it require multiple (nested) query's? (or a combination of both) If all the rows are correctly sorted I can use php to determine which position it has in the tree if I run by each row one by one. I could at least group by parent_id but sorting in the quiry can hardly be done because the id itself tells nothing about the position or sequence in the tree. If the prev_sibling=0 it could be placed first and if next_silbing=0 it could be placed last (per parent group) but again you have no controle over the rows in between, am I right about this so far? What would be the best solution? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Php datetime
Daniel Clark wrote: i have created a mysql table where i would like to store the date and time whenever a user logs into a web site. the login is done through php/mysql as well but i'm not sure what the php code is to add the current date/time into a mysql database. the field in the db is created as a 'datetime' field. I've heard you want to use a TIMESTAMP field for that. The MySQL TIMESTAMP is actually a pretty lousy column type. The only interesting thing that the TIMESTAMP column can do is auto-update, but this is pretty easy to do either (1) using MySQL's NOW() function or (2) formatting a php (unix) timestamp. (1) Use MySQL's NOW() function: INSERT INTO mytable (col1, datetime_col) VALUES ('blah', NOW()) (2) Format the date using PHP's date() function: $sql = INSERT INTO mytable (col1, datetime_col) VALUES ('blah', '.date('Y-m-d H:i:s', time()).') I would recommend the MySQL method unless you have a real reason to use PHP for the stamp. Oh, I almost forgot. Why is TIMSTAMP bad? Because (1) it has nothing to do with the meaning of 'timestamp' in any other DB and (2) it's a real pain to parse the value of a MySQL TIMESTAMP for use in your PHP application. TIMESTAMP is in the format YYYMMDDHHMMSS (e.g. 20040304100655). strtotime() will not deal with that value. It looks like a number. The only way (I've found) to turn that into something readable is to use MySQL time/date functions to format it or to use regular expressions (or other string parsing in PHP). Using the date functions really binds you to using MySQL and also makes it impossible to format the date in another language/locale. IMO, formatting dates in SQL is just plain bad practice. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Database Search Engine
There are fulltext search engines like mnoGoSearch (http://mnogosearch.org) that can index database tables like filesystems. You could then search accross all of your databases -- and you could also include results from mnogo spidering in that search if appropriate. This might be a good solutions for your problem. Hans P.S. When you start a new topic on the list, you should not reply to an existing email, since people using mail programs that support threading will not see your messages as a new topic. Muhammed Mamedov wrote: Hi, I have many databases, each full up of more than 20 tables. I need to perform search based on search criteria entered by users. Does anyone know effective way of performin this task? Waiting for your comments. Regards, Muhammed Mamedov -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Database abstraction
Martin Marques wrote: Mensaje citado por Malte Starostik [EMAIL PROTECTED]: Hi, I've read several posts touching this subject, but I didn't find one that asks a simple question I've been wondering about since I first used PHP and that is also one of the two really really bad things about PHP (the other being lack of OO): PHP5 deals with this. http://ar.php.net/manual/en/faq.general.php#faq.general.relation-versions Yes, and also if you are using PHP5, you may want to consider using a new DB abstraction layer: Creole (http://creole.phpdb.org). Creole is based on the API for JDBC. It currently supports MySQL, PostgreSQL, SQLite, MS SQL Server, and we have preliminary support for Oracle. Distinctive features include: * built for PHP5, uses new object model, Exceptions * fully object-oriented API (ResultSet class, metadata classes) * Very complete database metadata (table, full column info, pimary keys, foreign keys, indexes) * Complex unified type system (based on JDBC Types) * Simple, un-exceptional handling of LOBs * type-specific methods handle any necessary conversions and escaping for inserting and retriving values. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] International Addresses and Telephones
I have a form which captures US addresses ( address, city, state ) and telephone numbers ( 10 digit ) and am writing this data to a MySQL table. I need to expand this to capture non-US info, so I was hoping for some guidance on forms and table structures. I have googled this to death and still haven't come up with anything. Ummm ... did you try e-commerce sites in the target country/ies ? E.g. alapage.com for France, bol.it ... bol.de ... safe bet: try amazon.com in appropriate country. (amazon.fr, etc.) Hans smime.p7s Description: S/MIME Cryptographic Signature
Re: [PHP-DB] convert date in german format
how can I convert a date stored in a mysql-database for output in the german format (dd.mm.yy). I tried date (d.m.y,$row-from); the second parameter to date() should be a unix timestamp (integer). Did you convert your MySQL date to a timestamp? Try: date(d.m.y, strtotime($row-from)); if that doesn't work (strtotime will return -1 if it fails, which will end up looking like 31.12.69 when date() renders it), then you may need to either a) use MySQL's UNIX_TIMESTAMP() function in your query to convert your date to a unix timestamp or b) use preg_match() and mktime() to create the unix timestamp from the MYSQL data. Hans smime.p7s Description: S/MIME Cryptographic Signature
Re: [PHP-DB] convert date in german format
at the top of your script, do: setlocale ( LC_TIME, 'de_DE' ) ; (assumes your machine has German support installed) Yes, this is really the right way to do date display for different locales, but it also requries that you use the strftime() function instead of date(). Like date(), strftime() will expect the second param to be a unix timestamp, so you still have to deal with that issue. strftime() will provide much more flexibility in displaying german dates -- e.g. ability to have German days of week, month names, etc. Using MySQL DATE_FORMAT() would work, but it's a hack solution; if you ever wanted to create an English version of your site you would have to make changes to your data-layer queries -- and that's just not where that logic belongs. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: nested sets?
You may want to try the famous Joe Celko's approach that he described in this book: Book review: SQL for Smarties http://www.phpclasses.org/products.html/id/1558605762.html Also, be sure to check our the PEAR classes DB_NestedSet/MDB_NestedSet which will make using this model much, much easier. But do buy the book, because it's awesome. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php