Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables
Hi, Did you try to use something like this. mysql_query(SELECT DISTINCT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM ((WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID) LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID) LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); The other thing that may help is to rearange the order of the tables in the FROM clause. Please check if some of the joins return more than one result - if you have more than one address for a publisher the query will return one row for every address. Hope this helps Dobromir Velev -Original Message- From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, July 12, 2001 4:31 AM Subject: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hello, I posted this to the general list this morning got a couple of good leads, but they weren't able to actually fix the problem, so I'm posting here to the db list. I'm making some headway on joining three MySQL tables. However, when I run this query: mysql_query(SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); I now get results in triplicate. ie. I'm getting three copies of the same title, firstName, organization, etc I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure how to return a query without duplication. This is far better than what I had this morning (which was no response from the server). Thanks. I'm new to joining tables... Someone wrote back suggesting that SELECT DISTINCT could be used to to the job. Another person suggested that using UNIQUE(profileID) would make it look nicer. I wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to WLPbib.. Any suggestions would be useful. Mike -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables
What about... SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROMWLPprofile, WLPaddress, WLPcountry WHERE WLPprofile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I had a similar problems with a database I was working with... It had a main table with 29,000 listings, and I decided to normalize it to improve query times (split the records into diff. tables, one for each attribute of the record, associate the records back together by a common ID than spanned all the tables)... I ended up with an SQL query that spanned like 10 tables-- but it was -way- faster than one big table. There's an excellent article on Normalization on PHPbuilder -- http://www.phpbuilder.com/columns/barry2731.php3 If your WLP tables are very large, you may want to try using mySQL's EXPLAIN SELECT [rest of select query]... function to figure out the best (read: efficient) ways of performing this query... Indexes are definitely a must if you are dealing with a lot of rows... If not, you should be just fine with the above query-- which I -think- is equivilent... I don't have a ton of experience with SQL, so perhaps someone can better elaborate. Best of luck! -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 3:01 AM To: [EMAIL PROTECTED]; Mike Gifford Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hi, Did you try to use something like this. mysql_query(SELECT DISTINCT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM ((WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID) LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID) LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); The other thing that may help is to rearange the order of the tables in the FROM clause. Please check if some of the joins return more than one result - if you have more than one address for a publisher the query will return one row for every address. Hope this helps Dobromir Velev -Original Message- From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, July 12, 2001 4:31 AM Subject: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hello, I posted this to the general list this morning got a couple of good leads, but they weren't able to actually fix the problem, so I'm posting here to the db list. I'm making some headway on joining three MySQL tables. However, when I run this query: mysql_query(SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); I now get results in triplicate. ie. I'm getting three copies of the same title, firstName, organization, etc I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure how to return a query without duplication. This is far better than what I had this morning (which was no response from the server). Thanks. I'm new to joining tables... Someone wrote back suggesting that SELECT DISTINCT could be used to to the job. Another person suggested that using UNIQUE(profileID) would make it look nicer. I wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to WLPbib.. Any suggestions would be useful. Mike -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To
Re: [PHP-DB] Paging help needed :-(
Hi, First you'll have to modify your query to show only ten rows. $sql = SELECT id, email, name, subject, url, image2, comments, dat FROM users ORDER BY id DESC LIMIT $start,10; The $start variable shows the number of the first row to show The links for the previous and next page will be something like this ? if ($start=10)echo a href='your_page.php3?.($start-10).'Previous/a; echo a href='your_page.php3?.($start+10).'Next/a; ? You can play around with this code to allow the users to go directly to a specific page, not to show the Next link when there are no more records to show, allow users to define how many records to show in a page and etc. Dobromir Velev -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, July 12, 2001 7:53 AM Subject: [PHP-DB] Paging help needed :-( Hi there Everyone, I currently have a dedicated Apache server with PHP 4.06, MySQL etc .. installed and running fine. Below is alittle peice of code that I would love an answer to: ?php $connection = mysql_connect(Localhost,username!!!,password!!!) or die(Couldn't make a connection.); $db = mysql_select_db(planet, $connection) or die(Couldn't select database.); $sql = SELECT id, email, name, subject, url, image2, comments, dat FROM users ORDER BY id DESC; $sql_result = mysql_query($sql,$connection) or die(Couldn't execute query.); ? ? while ($row = mysql_fetch_array($sql_result)) { $email = $row[email]; $name = $row[name]; $subject = $row[subject]; $url = $row[url]; $image2 = $row[image2]; $comments = $row[comments]; $dat = $row[dat]; $comments=nl2br($comments); include(censorguestbook.php); $ip = getenv (REMOTE_ADDR); ? then all the HTML and display bits (Example at www.planetoxygene.com in the guestbook). ? } mysql_free_result($sql_result); mysql_close($connection); ? Now my question is, how do I do paging in PHP? I need to display 10 entries per page, and be able to go back and forth with them. I'm relatively new to PHP so I would be really grateful for any advice. Thanks everyone, I appreciate it. Chris Payne www.planetoxygene.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Row order
Stefan, Depending on your load, you could save the results in a database table, make the table name a session variable, and just have the sorting column names be fields in the saved table. CREATE TABLE Q1234 AS SELECT ...; You can also put all the variables in a variable then put the new variable in the a tags like this: $query_vars = field1=afield2=bfield3=c...; a href=file.php??=$query_vars?sort=field4Field 4/a Hope this helps. Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Hello
Hello I have an ibm db2 which has jpg images stored as blob fields (about 2M). I seem to have a hard time getting them with PHP and presenting them properly in the browser. (When I try to save it from the DB2 side, it is stored ok). Though i actually get the image i ask for, when i try to output it to the browser i get a series of characters from 0 to F instead (Hex) like this: FFD8FFFE000857414E473202FFE000104A4649460001010102580258FFDB0 Saving it to a file with has no result either. The file seems to be created, it has the appropriate size but trying to view is contents results in getting an unsupported type of image which the browser fails to present. Using the different types of the odbc_binmode function of PHP (http://www.php.net/manual/en/function.odbc-binmode.php), returns the same results. I try odbc_longreadlen since i get a blob field but the output is similar to the previous. I also asked in a DB2 newsgroup http://groups.google.com/groups?hl=elsafe=offic=1th=17ccf5cbc4d0762c,4se ekm=5261b6a0.0107090324.6ab27060%40. Thats why we added a field containing a JPEG format of the image but it doesnt seem to work either. Thanks in advance. Dimitris Glezos High Performance Computing Laboratory University of Patras -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: Splitting Article Pages
hello! exactly esterday i had the same problem ;) i did it like this, using wordwrap/explode functions: ... $CFG_max_page_length = 5000; ... if(@mysql_num_rows($rez) 0) { $text = nl2br(mysql_result($rez,0,text)); $text_length = strlen($text); if($text_length $CFG_max_page_length) { $page_count = ceil($text_length/$CFG_max_page_length); $text = wordwrap($text, $CFG_max_page_length, __SPLIT__, 0); $text_arr = explode(__SPLIT__,$text); $p = (!isset($p) || !($p0)) ? 1 : (int)$p; for ($page=1; $page=$page_count; $page++) { $pages .= ($page != $p) ? a href=$PHP_SELF?a=$ab=$bp=$page$page/a : b$page/b ; } $pages = p align=rightb$STR_pages:/bnbsp; $pages/p; $text = $text_arr[$p-1]; } echo $pages p$text/p $pages; http://demo.mp.lv/guru/index.php?a=1b=17p=1 its upcoming vortal about psyhology/metaphiscs/health in russian which i afraid i never complete by the deadline.. :/ Jordan Elver [EMAIL PROTECTED] wrote Hi, I need to split an article over several pages. I know how to create next and previous links for database queries. But how can I pull out say, an article from a database and split the article text over a certain amount of pages depending on the article length, so every 500 words and new page is created? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Need a shopping cart
check out the session cart on phpbuilder.com it stores all the cart stuff as session vars so you can use any db Steve -Original Message- From: Jeff @ HookedOnThe.Net [mailto:[EMAIL PROTECTED]] Sent: 12 July 2001 02:47 To: [EMAIL PROTECTED] Subject: [PHP-DB] Need a shopping cart Can anyone direct me to a shopping cart package (preferrably in PHP, although PERL would possibly work) which utilizes either an MS Access database or flat files? I've found several shopping carts, but they all seem to require MySQL which I do not have access to on my server. My server is Windows NT 4 with PHP 4 and PERL 5. I sure hope someone can help. Thanks in advance. Regards, Jeff [EMAIL PROTECTED] - ICQ UIN: 736807 Training, Web Hosting and Design http://www.HookedOnThe.Net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] I need a User Authentication solution
http://www.phpsecurepages.f2s.com/ I have tried this and it works great. I am very much a beginner so you shouldn't have any problems, just read the README file thoroughly. All the Best Lisa -Original Message- From: David Coleman [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 1:38 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] I need a User Authentication solution Hello, I'm looking for a complete User Authentication solution. Kind of like the ASP solution listed below: http://www.powerasp.com/content/code-snippets/advanced-password-protection.a sp However, I'd like the solution to run on Linux w/ PHP and MySQL. Does anyone know of an off-the-shelf password protection script I could purchase? Or, can someone help me develop one. Thanks! -David -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables
Hi Matthew, Matthew Loff wrote: What about... SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPprofile, WLPaddress, WLPcountry WHERE WLPprofile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I think that this is working now. I had a similar problems with a database I was working with... It had a main table with 29,000 listings, and I decided to normalize it to improve query times (split the records into diff. tables, one for each attribute of the record, associate the records back together by a common ID than spanned all the tables)... This one will likely have 10,000 to start with, so it is good to build in normalization... Didn't even know what the term normalization referred to earlier today. However, that is what I was doing with the table. I ended up with an SQL query that spanned like 10 tables-- but it was -way- faster than one big table. There's an excellent article on Normalization on PHPbuilder -- http://www.phpbuilder.com/columns/barry2731.php3 This is a good article. Even addressed the question that I fired off to Dobromir about linking various profiles to the same bibliography. One person can write many articles an article can have many authors, so I need to create a many-many table to link the articles to profiles. I think that this wil work: CREATE TABLE WLParticle2profile ( a2pID mediumint(9) NOT NULL auto_increment, bibID mediumint(9), profileID mediumint(9), PRIMARY KEY (a2pID) ) I'll then need to Re-jig the WHERE command to limit the number of returns... This would become SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROMWLPprofile, WLPaddress, WLPcountry, WLParticle2profile WHERE WLParticle2profile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I'm not sure this will work to tie in the relational table and then we come back to the problem with duplicate entries again... If your WLP tables are very large, you may want to try using mySQL's EXPLAIN SELECT [rest of select query]... function to figure out the best (read: efficient) ways of performing this query... Indexes are definitely a must if you are dealing with a lot of rows... If not, you should be just fine with the above query-- which I -think- is equivilent... I didn't know about this option: http://www.mysql.com/doc/E/X/EXPLAIN.html I find the MySQL.com site to be hard to read through.. php.net is much easier to understand in my experience. I don't have a ton of experience with SQL, so perhaps someone can better elaborate. I think that you did a good job.. However I think I'm still stuck with the same duplicate error now (well when I've expanded the code. Mike -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 3:01 AM To: [EMAIL PROTECTED]; Mike Gifford Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hi, Did you try to use something like this. mysql_query(SELECT DISTINCT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM ((WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID) LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID) LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); The other thing that may help is to rearange the order of the tables in the FROM clause. Please check if some of the joins return more than one result - if you have more than one address for a publisher the query will return one row for every address. Hope this helps Dobromir Velev -Original Message- From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, July 12, 2001 4:31 AM Subject: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hello, I posted this to the general list this morning got a couple of good leads, but they weren't able to actually fix the problem, so I'm posting here to the db list. I'm making some headway on joining three MySQL tables.
[PHP-DB] Order by unix timestamp
Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Order by unix timestamp
ORDER BY unixtime DESC (it default orders by asc, not desc) Greetinx, Mike Michael Rudel - Web-Development, Systemadministration - Besuchen Sie uns am 20. und 21. August 2001 auf der online-marketing-düsseldorf in Halle 1 Stand E 16 ___ Suchtreffer AG Bleicherstraße 20 D-78467 Konstanz Germany fon: +49-(0)7531-89207-17 fax: +49-(0)7531-89207-13 e-mail: mailto:[EMAIL PROTECTED] internet: http://www.suchtreffer.de ___ -Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 5:09 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Escaping
Hello, I have a table in a MySQL DB (RH Linux/Apache) with a field called customer_name. Some of the customer names have an ampersand in them (ie; X X Supply). I am performing a select statement on this table to create a sales summary with customer name and total sales. This works fine; however, I am also creating a dynamic link on the customer name so that the user can click on it to get a detailed report of sales to that customer. This works great EXCEPT for the customers with ampersands in thier names. The result of clicking on these customers indicates a No Records found for X where X is the letter immediately preceding the ampersand. Does anyone know how to get around this? TIA, Randy Rankin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Order by unix timestamp
-Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: 12 July 2001 16:09 To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? order by unixtime desc Steve -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Order by unix timestamp
Vielen Dank Michael Rudel [EMAIL PROTECTED] schrieb im Newsbeitrag 02bb01c10ae5$38128190$[EMAIL PROTECTED]">news:02bb01c10ae5$38128190$[EMAIL PROTECTED]... ORDER BY unixtime DESC (it default orders by asc, not desc) Greetinx, Mike Michael Rudel - Web-Development, Systemadministration - Besuchen Sie uns am 20. und 21. August 2001 auf der online-marketing-düsseldorf in Halle 1 Stand E 16 ___ Suchtreffer AG Bleicherstraße 20 D-78467 Konstanz Germany fon: +49-(0)7531-89207-17 fax: +49-(0)7531-89207-13 e-mail: mailto:[EMAIL PROTECTED] internet: http://www.suchtreffer.de ___ -Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 5:09 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Order by unix timestamp
Thank you Steve Brett [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... -Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: 12 July 2001 16:09 To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? order by unixtime desc Steve -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Escaping
When they click on the link what is happening? Is it doing a query in the database for all users with that name? If so you may want to do a str_replace('','\','customer_name') before your query. Basically you just want to add the \ before the ampersand. I haven't tried this but it SHOULD work. Roger On Thu, 12 Jul 2001, Rankin, Randy wrote: Hello, I have a table in a MySQL DB (RH Linux/Apache) with a field called customer_name. Some of the customer names have an ampersand in them (ie; X X Supply). I am performing a select statement on this table to create a sales summary with customer name and total sales. This works fine; however, I am also creating a dynamic link on the customer name so that the user can click on it to get a detailed report of sales to that customer. This works great EXCEPT for the customers with ampersands in thier names. The result of clicking on these customers indicates a No Records found for X where X is the letter immediately preceding the ampersand. Does anyone know how to get around this? TIA, Randy Rankin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] How to get a link to next db entry
Hi, I'm programming a forum. Every RE: entry has a parent_id which is id of the parent entry. How could I say this is the Link to the next db_entry? The id of the comments are not in order because not every Person writes a RE: entry - he could start a new thread. Thanks for your help Hoth -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Clock struggles
Im running PHP off of Win2k. The clock on the server is set correctly, but somehow PHP timestamps are adding 7 hours to that. Is there a time zone property that needs to be changed?
Re: [PHP-DB] Hello
The page you gave me didnt help but thanks anyway :) We solved the problem which was dizzing us for days by using the silly command pack(H*, $image) !! Dimitris Ben Bleything [EMAIL PROTECTED] wrote in message 01c10ae1$30a03690$0201a8c0@c1141975c">news:01c10ae1$30a03690$0201a8c0@c1141975c... Check out http://www.phpbuilder.com/columns/florian19991014.php3. It deals with MySQL, but the concepts should be the same... it sounds like there are some steps about handling the binary data in a binary-safe fashion that are getting left out. Good luck, Ben -Original Message- From: a [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 2:33 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Hello Hello I have an ibm db2 which has jpg images stored as blob fields (about 2M). I seem to have a hard time getting them with PHP and presenting them properly in the browser. (When I try to save it from the DB2 side, it is stored ok). Though i actually get the image i ask for, when i try to output it to the browser i get a series of characters from 0 to F instead (Hex) like this: FFD8FFFE000857414E473202FFE000104A4649460001010102580258FFDB0 Saving it to a file with has no result either. The file seems to be created, it has the appropriate size but trying to view is contents results in getting an unsupported type of image which the browser fails to present. Using the different types of the odbc_binmode function of PHP (http://www.php.net/manual/en/function.odbc-binmode.php), returns the same results. I try odbc_longreadlen since i get a blob field but the output is similar to the previous. I also asked in a DB2 newsgroup http://groups.google.com/groups?hl=elsafe=offic=1th=17ccf5cbc4d0762c, 4se ekm=5261b6a0.0107090324.6ab27060%40. Thats why we added a field containing a JPEG format of the image but it doesnt seem to work either. Thanks in advance. Dimitris Glezos High Performance Computing Laboratory University of Patras -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Thank you everyone :-)
Here,,Here I second that emotion Ken - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 12, 2001 12:52 PM Subject: [PHP-DB] Thank you everyone :-) Hi there, Thanks everyone for your help on this list - it really is a credit to the PHP community to have you all out there helping those of us who don't know what the heck we are doing (But who are trying to learn :-) I appreciate it, and I am sure many others do too. Regards Chris Payne www.planetoxygene.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables
Mike-- I hate to suggest quick fixes :) but if all else fails, you can add a DISTINCT to the query. I've had to do it before... If you keep running into problems, and EXPLAIN ... doesn't resolve them, then perhaps you could send us a dump of the db structure... # mysqldump -d [database_name] dumpfile.sql Or if you need a UN/PW: # mysqldump -d [database_name] -u[username] -p dumpfile.sql That will dump the structure of the db, but not the actual data. Good luck! -Original Message- From: Mike Gifford [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 10:48 AM To: Matthew Loff Cc: 'Dobromir Velev'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hi Matthew, Matthew Loff wrote: What about... SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPprofile, WLPaddress, WLPcountry WHERE WLPprofile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I think that this is working now. I had a similar problems with a database I was working with... It had a main table with 29,000 listings, and I decided to normalize it to improve query times (split the records into diff. tables, one for each attribute of the record, associate the records back together by a common ID than spanned all the tables)... This one will likely have 10,000 to start with, so it is good to build in normalization... Didn't even know what the term normalization referred to earlier today. However, that is what I was doing with the table. I ended up with an SQL query that spanned like 10 tables-- but it was -way- faster than one big table. There's an excellent article on Normalization on PHPbuilder -- http://www.phpbuilder.com/columns/barry2731.php3 This is a good article. Even addressed the question that I fired off to Dobromir about linking various profiles to the same bibliography. One person can write many articles an article can have many authors, so I need to create a many-many table to link the articles to profiles. I think that this wil work: CREATE TABLE WLParticle2profile ( a2pID mediumint(9) NOT NULL auto_increment, bibID mediumint(9), profileID mediumint(9), PRIMARY KEY (a2pID) ) I'll then need to Re-jig the WHERE command to limit the number of returns... This would become SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROMWLPprofile, WLPaddress, WLPcountry, WLParticle2profile WHERE WLParticle2profile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I'm not sure this will work to tie in the relational table and then we come back to the problem with duplicate entries again... If your WLP tables are very large, you may want to try using mySQL's EXPLAIN SELECT [rest of select query]... function to figure out the best (read: efficient) ways of performing this query... Indexes are definitely a must if you are dealing with a lot of rows... If not, you should be just fine with the above query-- which I -think- is equivilent... I didn't know about this option: http://www.mysql.com/doc/E/X/EXPLAIN.html I find the MySQL.com site to be hard to read through.. php.net is much easier to understand in my experience. I don't have a ton of experience with SQL, so perhaps someone can better elaborate. I think that you did a good job.. However I think I'm still stuck with the same duplicate error now (well when I've expanded the code. Mike -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 3:01 AM To: [EMAIL PROTECTED]; Mike Gifford Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hi, Did you try to use something like this. mysql_query(SELECT DISTINCT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM ((WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID) LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID) LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID);
Re: [PHP-DB] Clock struggles
are you using the GMT time functions or localtime functions? gmmtime, gmdate and gmstrftime are all GMT time, localtime, mktime, strftime use local time... Taylor \Cody\ Fletcher wrote: Im running PHP off of Win2k. The clock on the server is set correctly, but somehow PHP timestamps are adding 7 hours to that. Is there a time zone property that needs to be changed? -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Escaping
try using the SUBSTR function to check each string for the first char as a . if it does have it, strip and continue the process... Rankin, Randy wrote: Hello, I have a table in a MySQL DB (RH Linux/Apache) with a field called customer_name. Some of the customer names have an ampersand in them (ie; X X Supply). I am performing a select statement on this table to create a sales summary with customer name and total sales. This works fine; however, I am also creating a dynamic link on the customer name so that the user can click on it to get a detailed report of sales to that customer. This works great EXCEPT for the customers with ampersands in thier names. The result of clicking on these customers indicates a No Records found for X where X is the letter immediately preceding the ampersand. Does anyone know how to get around this? -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Order by unix timestamp
try the RSORT (reserve sort) function??? Andreas Iwanowski wrote: Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Production: Linux/Apache/PHP -- FreeTDS -- W2K/MSSQL 7.0
-Original Message- From: E. Peter K. Chan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 11, 2001 11:32 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Production: Linux/Apache/PHP -- FreeTDS -- W2K/MSSQL 7.0 When I switch to production I intend to use: Linux/Apache/PHP -- FreeTDS -- W2K/MSSQL7.0 I'm currently using this combination (except with FreeBSD instead of Linux). I am particularly concerned about the mssql_next_result function (such a function doesn't exist for Sybase and I don't know enough about how FreeTDS works). The mssql functions that get enabled by the FreeTDS library are specified as aliases for the equivalent sybase functions in $PHPDIR/ext/sybase/php_sybase_db.c. It doesn't look like mssql_next_result is among those that have an equivalent, so it's not included. (The ones that exist are: mssql_connect, mssql_pconnect, mssql_close, mssql_select_db, mssql_query, mssql_free_result, mssql_get_last_message, mssql_num_rows, mssql_num_fields, mssql_fetch_row, mssql_fetch_array, mssql_fetch_object, mssql_data_seek, mssql_fetch_field, mssql_field_seek, mssql_result, mssql_affected_rows, mssql_min_error_severity, and mssql_min_message_severity.) --- Mark Roedel | The most overlooked advantage to owning a Systems Programmer| computer is that if they foul up there's no LeTourneau University | law against whacking them around a little. Longview, Texas, USA | -- Owen Porterfield -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] I need a User Authentication solution
if you are using apache, you try the .htaccess file authorization technique... are you concerned about sending the username and password via plain text through the wire? if yes, then you may have to implement SSL on your server... David Coleman wrote: Hello, I'm looking for a complete User Authentication solution. Kind of like the ASP solution listed below: http://www.powerasp.com/content/code-snippets/advanced-password-protection.asp However, I'd like the solution to run on Linux w/ PHP and MySQL. Does anyone know of an off-the-shelf password protection script I could purchase? Or, can someone help me develop one. Thanks! -David -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Random Password Generation/MSSQL
Having an issue with this script, the generator works if I just echo out the password, however when I attempt to update the table, it times-out in IIS. I've bumped the IIS Timeout up to over 20 minutes, and it still times out. This is on a database with approximately 29,000 entries. Any ideas? Ryan function rand_pass() { $array = array( a,b,c,d,e,f,g,h,i,j,k,l, m,n,o,p,q,r,s,t,u,v,w,x,y, z,1,2,3,4,5,6,7,8,9 ); $num_letters = 10; $uppercased = 3; mt_srand ((double)microtime()*100); for($i=0; $i$num_letters; $i++) $pass .= $array[mt_rand(0, (count($array) - 1))]; for($i=1; $istrlen($pass); $i++) { if(substr($pass, $i, 1) == substr($pass, $i-1, 1)) $pass = substr($pass, 0, $i) . substr($pass, $i+1); } for($i=0; $istrlen($pass); $i++) { if(mt_rand(0, $uppercased) == 0) $pass = substr($pass,0,$i) . strtoupper(substr($pass, $i,1)) . substr($pass, $i+1); } $pass = substr($pass, 0, $num_letters); return($pass); } MSSQL_CONNECT(XXX,XX,X); MSSQL_SELECT_DB(X); $query=SELECT * FROM TABLE; $return=MSSQL_QUERY($query); $count=MSSQL_NUM_ROWS($return); $insertquery=UPDATE TABLE SET FIELD='$password' WHERE DUNSNumber='$DUNSNumber'; $row=MSSQL_FETCH_OBJECT($return); for($i=1;$i=$count;$i++){ $DUNSNumber=$row-DUNSNumber; echo rand_pass().br; MSSQL_QUERY($insertquery); } MSSQL_CLOSE(); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Random Password Generation/MSSQL
-Original Message- From: Ryan Marrs [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 1:54 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Random Password Generation/MSSQL Having an issue with this script, the generator works if I just echo out the password, however when I attempt to update the table, it times-out in IIS. I've bumped the IIS Timeout up to over 20 minutes, and it still times out. This is on a database with approximately 29,000 entries. Any ideas? [snip] for($i=1;$i=$count;$i++){ $DUNSNumber=$row-DUNSNumber; echo rand_pass().br; I bet if you echo $insertquery right here, you won't see what you were expecting to. (Hint: $password and $DUNSNumber aren't set when you give $insertquery its value; neither is the string updated for each iteration of this loop.) MSSQL_QUERY($insertquery); } Perhaps that's not the entire problem, but it can't be helping...if nothing else, once that's corrected maybe you can start looking at doing those updates in smaller batches. 20 minutes does seem a bit extreme, but then it's hard to make much of a judgment on that without knowing more about the machine in question, what else it's being used for besides sql services, how heavily it's being utilized, etc. --- Mark Roedel | Nothing in life is so bad that it cannot be Systems Programmer| made much, much worse by the addition of LeTourneau University |lots of spikes and razors. Longview, Texas, USA |-- Sean Kozma -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Memory Issues with MySQL 4.0.6
Hi. PHP crashes every time we run a certain batch of reports since we upgraded from 4.0.4pl1 to 4.0.6. The report is ridiculously complex and requires a boat-load of database queries. They ran very well on the previous versions of PHP. Information: --The script tanks after the 34th query, at which point it begins displaying: Supplied argument is not a valid MySQL result resource. --We have tried the script using: *one connection for the entire script (mysql_pconnect) *recalling mysql_pconnect() before every query without mysql_close() *new connections for every query (mysql_connect...mysql_close) --Same outcome with Mysql versions 3.23.39, 3.23.38 and 3.23.28 --Works perfectly well in PHP 4.04pl1, but not at all 4.06 --Debug log repeatedly contains two messages: FATAL: erealloc(): Unable to allocate 1515870836 bytes [Wed Jul 11 09:12:17 2001] [notice] child pid 16719 exit signal Segmentation fault (11) - --- ./zend_execute.c(358) : Block 0x083FFD78 status: zend_variables.c(62) : Actual location (location was relayed) Beginning: OK (allocated on zend_API.c:379, 10 bytes) End: Overflown (magic=0x2A8FCC82 instead of 0x2A8FCC84) 1 byte(s) overflown - --- I have tried lots of troubleshooting approaches, far too many to mention now. I have also searched the mailing lists and bug reports and have read documents all over the place, all to no avail. If I am being an idiot here, let me know and I'll go away. Thank you for your time and help, Chris Chew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Losing session variable with db session handler
Hi all, I tried this question on L-PHP about a week ago and got no replies so I thought I'd give it a shot here: I have a session handler (PHP4) that saves session info to a MySQL database. The problem is that the sessionID changes everytime a new page is loaded. I have cookies on and the sessionID automatically gets appended to relative links but the appended ID is what changes. I'd love it if someone can point me in the right direction. Thnx!.. aral :) __ ([EMAIL PROTECTED]) New Media Producer, Kismia, Inc. ([EMAIL PROTECTED]) Adj. Prof., American University ¯¯ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] data consistency
Hello php-db, is PHP / Mysql could handle multiple access into table to keep data consistency ? ,,, (@-@) +==---o00(_)00o-==+ It is better to be defeated on principle than to win on lies. --Arthur Calwell -- Best regards, andRie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: Escaping
On Thu, 12 Jul 2001 10:18:23 -0500, [EMAIL PROTECTED] (Randy Rankin) wrote: Hello, I have a table in a MySQL DB (RH Linux/Apache) with a field called customer_name. Some of the customer names have an ampersand in them (ie; X X Supply). I am performing a select statement on this table to create a sales summary with customer name and total sales. This works fine; however, I am also creating a dynamic link on the customer name so that the user can click on it to get a detailed report of sales to that customer. This works great EXCEPT for the customers with ampersands in thier names. The result of clicking on these customers indicates a No Records found for X where X is the letter immediately preceding the ampersand. Does anyone know how to get around this? Would it be practical to link off a unique record_id instead of the noun-name? That way you can assure that the data you're passing doesn't have any URL-unfriendly characters, and you guarantee that the data you're retrieving is the unique data you're linking from. Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Paging help needed :-(
Here is some code that I like to use. I mix real code and psuedo code cause I'm more used to just using the db class from PHP Lib. First get a count of how many records there are. $sql = SELECT count(*) as c FROM users; Then read that count into variable $count. Declare a variable which will contain the number of records per page, this is a really good idea cause if that number changes later you just have to change one variable instead of hunting down every instance of that number. $records_per_page = 10; Next figure out how many pages you'll have. $total_pages = ceil( $count / $records_per_page ); This just sets up a page variable which will be past in a next and prev link. The first link to this page may not actually have $page so this just sets it up. if ( !isset($page) ) { $page = 1; } I like to setup a little navigation that has First | Prev | Next | Last on the page with each word being a link or not depending on what page you are on. To do this I use the following piece of code. # this will set up the First | Prev | part of our navigation. if ( $page == 1 ) { # if we are on the first page then First and Prev # should not be links. $naviagation = font color=\#66\First/font | font color=\#66\Prev/font | ; } else { # we are not on page one so First and Prev can # be links $prev_page = $page - 1; $navigation = a href=\your_page.php?page=1\First/a | a href=\your_page.php?page=$prevpage\Prev/a | ; } # this part will set up the rest of our navigation Next | Last if ( $page == $total_pages ) { # we are on the last page so Next and Last # should not be links $navigation .= font color=\#66\Next/font | font color=#66Last/font; } else { # we are not on the last page so Next and Last # can be links $next_page = $page + 1; $navigation .= a href=\your_page.php?page=$next_page\Next/a | a href=\your_page.php?page=$total_pages\Last/a; } With your navigation built you can just echo that out any where you want it to appear. The final thing to do is your actual query, but first we have to figure out what the offset will be. $offset = ( $page - 1 ) * $total_pages; $sql = SELECT id, email, name, subject, url, image2, comments, dat FROM users ORDER BY id DESC LIMIT $offset,$records_per_page; And that's it? Simple eh? You may also want to put out something like: echo $page of $total_pages; Enjoy. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 12, 2001 3:49 AM Subject: [PHP-DB] Paging help needed :-( Hi there Everyone, I currently have a dedicated Apache server with PHP 4.06, MySQL etc .. installed and running fine. Below is alittle peice of code that I would love an answer to: ?php $connection = mysql_connect(Localhost,username!!!,password!!!) or die(Couldn't make a connection.); $db = mysql_select_db(planet, $connection) or die(Couldn't select database.); $sql = SELECT id, email, name, subject, url, image2, comments, dat FROM users ORDER BY id DESC; $sql_result = mysql_query($sql,$connection) or die(Couldn't execute query.); ? ? while ($row = mysql_fetch_array($sql_result)) { $email = $row[email]; $name = $row[name]; $subject = $row[subject]; $url = $row[url]; $image2 = $row[image2]; $comments = $row[comments]; $dat = $row[dat]; $comments=nl2br($comments); include(censorguestbook.php); $ip = getenv (REMOTE_ADDR); ? then all the HTML and display bits (Example at www.planetoxygene.com in the guestbook). ? } mysql_free_result($sql_result); mysql_close($connection); ? Now my question is, how do I do paging in PHP? I need to display 10 entries per page, and be able to go back and forth with them. I'm relatively new to PHP so I would be really grateful for any advice. Thanks everyone, I appreciate it. Chris Payne www.planetoxygene.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Escaping
Please explain the dynamic link . is that in the documentation somewhere? Kne - Original Message - From: Rankin, Randy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 12, 2001 8:18 AM Subject: [PHP-DB] Escaping Hello, I have a table in a MySQL DB (RH Linux/Apache) with a field called customer_name. Some of the customer names have an ampersand in them (ie; X X Supply). I am performing a select statement on this table to create a sales summary with customer name and total sales. This works fine; however, I am also creating a dynamic link on the customer name so that the user can click on it to get a detailed report of sales to that customer. This works great EXCEPT for the customers with ampersands in thier names. The result of clicking on these customers indicates a No Records found for X where X is the letter immediately preceding the ampersand. Does anyone know how to get around this? TIA, Randy Rankin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Escaping
dynamic link, i think he means something like echo a href=\customer_details.php?customer=$customer\Customer/a\n; or whatever, and as for making it so that you can include a in the $customer variable in the example above, try this; echo a href=\customer_details.php?customer= . urlencode($customer) . \Customer/a\n; /beau // -Original Message- // From: Ken Sommers [mailto:[EMAIL PROTECTED]] // Sent: Friday, 13 July 2001 11:27 AM // To: Rankin, Randy; [EMAIL PROTECTED] // Subject: Re: [PHP-DB] Escaping // // // Please explain the dynamic link . // is that in the documentation somewhere? // // Kne // - Original Message - // From: Rankin, Randy [EMAIL PROTECTED] // To: [EMAIL PROTECTED] // Sent: Thursday, July 12, 2001 8:18 AM // Subject: [PHP-DB] Escaping // // // Hello, // // I have a table in a MySQL DB (RH Linux/Apache) with a field called // customer_name. Some of the customer names have an // ampersand in them (ie; X // // X Supply). I am performing a select statement on this // table to create a // sales summary with customer name and total sales. // // This works fine; however, I am also creating a dynamic link on the // customer // name so that the user can click on it to get a detailed // report of sales to // that customer. This works great EXCEPT for the customers // with ampersands // in // thier names. The result of clicking on these customers // indicates a No // Records found for X where X is the letter immediately // preceding the // ampersand. // // Does anyone know how to get around this? // // TIA, // // Randy Rankin // // -- // PHP Database Mailing List (http://www.php.net/) // To unsubscribe, e-mail: [EMAIL PROTECTED] // For additional commands, e-mail: [EMAIL PROTECTED] // To contact the list administrators, e-mail: // [EMAIL PROTECTED] // // // // -- // PHP Database Mailing List (http://www.php.net/) // To unsubscribe, e-mail: [EMAIL PROTECTED] // For additional commands, e-mail: [EMAIL PROTECTED] // To contact the list administrators, e-mail: // [EMAIL PROTECTED] // -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Escaping
I think he just means a hypertext anchor, that contains a string identifying the record. A HREF=fun.php?id=$id -Original Message- From: Ken Sommers [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 11:27 PM To: Rankin, Randy; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Escaping Please explain the dynamic link . is that in the documentation somewhere? Kne - Original Message - From: Rankin, Randy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 12, 2001 8:18 AM Subject: [PHP-DB] Escaping Hello, I have a table in a MySQL DB (RH Linux/Apache) with a field called customer_name. Some of the customer names have an ampersand in them (ie; X X Supply). I am performing a select statement on this table to create a sales summary with customer name and total sales. This works fine; however, I am also creating a dynamic link on the customer name so that the user can click on it to get a detailed report of sales to that customer. This works great EXCEPT for the customers with ampersands in thier names. The result of clicking on these customers indicates a No Records found for X where X is the letter immediately preceding the ampersand. Does anyone know how to get around this? TIA, Randy Rankin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Image Uploading into Database with ID
I don't know how many of you are familiar with Vb (www.vbulletin.com) but I am using the message board software and adding all that I can to it! :) I am wanting my users to be able to add a photo to their profiles. So I am trying to intergrate a upload script, that will upload the address to the image (stored on the site) into the correct ID number of the user. I have made it where it will upload the file to the correct folder and will put a link in the database, but I cannot figure it all out. Listed below is what I have in a .php file, but if you are familiar with the templates and everything with vb, then I will post the code I have that is not working. This code below works great.. but has not ability to add the image path or anything to the correct user with ID. --- code snippet = test.php --- form enctype=multipart/form-data method=post action=?php echo $PHP_SELF ? Upload a Photobr input type=File name=picture size=25 brbr input type=submit name=submit value=Upload /form ?php // Information needed to connect to the database define(SQL_SERVER, localhost); define(SQL_UID, USERNAME); define(SQL_PWD, PASSWORD); define(SQL_DB, DATABASE NAME); // After the file has been chosen and they click submit this will happen if ($submit) { $connection = mysql_pconnect(SQL_SERVER, SQL_UID, SQL_PWD); mysql_select_db(SQL_DB, $connection); $sql = INSERT INTO user (picture_name) . VALUES ('$picture_name'); mysql_query($sql); exec(cp $picture /root/to/the/images/$picture_name); // Echo's out the file that was upload to show that it succeeded. echo temp file: $picturebr\n; echo file name: $picture_namebr\n; echo file size: $picture_sizebr\n; echo file type: $picture_typebr\n; echo br\n; echo img src=images/$picture_namebr\n; return 0; } ? --- code snippet = test.php --- Here is what I altered the form code to look like for the modify and registration templates. --- code --- tr bgcolor={firstaltcolor} td valign=topnormalfontbUpload Picture :/bbr smallfontPlease select a photo from your hard drive to post on the site./smallfont/normalfont/td td valign=topinput type=File class=bginput name=picture_name size=25/td/tr --- end code --- I think for this to work properly it will have to not be called picture_name but something along the lines of $bbuserinfo[picture_name] to work... not for sure yet. After that here is some of the code I have that replaces the submission part of the above code. Since I added the form and everything to an already existing if action statement I just added the below code to that but.. it does not seem to work. --- code --- // Photo Upload if ($picture_name !=) { $picture_name=$picture_name; exec(cp $picture /root/to/the/images/$picture_name); } else { $picture_name=; } // End Photo Upload --- end code --- and for it to go along with the $bbuserinfo[picture_name] idea here is what the code should look like : --- code // Photo Upload if ($bbuserinfo[picture_name] !=) { $bbuserinfo[picture_name]=$bbuserinfo[picture_name]; exec(cp $picture /root/to/the/images/$picture_name); } else { $bbuserinfo[picture_name]=; } // End Photo Upload --- end code --- The if action already looks for an ID.. if 0 it fails, else it continues... so can anyone help me figure this one out... or tell me what a good command is for selecting the ID out of the database for the user that is logged in.. thanks BradC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Image Uploading into Database with ID
Hi! so last night I asked is there/how do you do a ocifetchstatement in mysql no one responded.. so this is what I ended up doing. function new_array($array) { // get the keys $array2 = $array; $keys = array_keys($array2); // now use the keys to get the values for($a=0;$acount($keys);$a++) { $the_key = $keys[$a]; $value = $array[$the_key]; // we got the value and the key now lets put em where we want em... $new_array = array( $keys[$a] = array( 0 = $value ) ); $newer_array = array_merge_recursive($newer_array, $new_array); } return($newer_array); } function my_data_array($query,$link) { global $debug; tracker($query); $result = mysql_query($query, $link); while($rows = mysql_fetch_array($result,MYSQL_ASSOC)) { if(!$array) { $array=new_array($rows); } else { $array = array_merge_recursive($array, $rows); } } return($array); } it returns an array that is just like the array returned from ocifetchstatement... so if you are migrating from oracle to mysql and you do not want to rewrite all our scripts... that use ocifetchstatement. try this on for size :-) hope this helps someone and err saves them some time... if anyone has a better way of doing this please send me the code. Jimmy Brake -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]