Re: [PHP-DB] Multiple Database Connection Using Prepard Statements
On 19 May 2012 20:36, Ron Piggott ron.pigg...@actsministries.org wrote: How do I connect to multiple mySQL databases using Prepared Statements ? I have the syntax === $dsh = 'mysql:host=localhost;dbname='.$database3; $dbh = new PDO($dsh, $username, $password); === I want to connect to $database1 without loosing my $database3 connection Thoughts? Comments? Create a new PDO object with the connection details you need? Regards Peter -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
*snip* How does set() know the difference between say the first row (1) + the fifth row (5) and the second row (2) + fouth row (4) in the bit set? The sum of both are the same. I am sure I am congfusing something. Ummm ... if you're asking how set figures out how bitmasks differ, then yes, you're very confused. It's a bitmask, summing plays no role. Rest assured that set will know the difference between different kinds of content in the column. Regards Peter -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
On Jan 11, 2012 7:13 AM, Karl DeSaulniers k...@designdrumm.com wrote: On Jan 10, 2012, at 10:49 PM, Karl DeSaulniers wrote: On Jan 10, 2012, at 9:30 AM, B. Aerts wrote: On 08/01/12 23:35, Karl DeSaulniers wrote: On Jan 8, 2012, at 10:36 AM, Bastien wrote: On 2012-01-08, at 7:27 AM, Niel Archer n...@chance.now wrote: -- Niel Archer niel.archer (at) blueyonder.co.uk Hello phpers and sqlheads, If you have a moment, I have a question. INTRO: I am trying to set up categories for a web site. Each item can belong to more than one category. IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc.. (Sorry no fancy box drawing) QUESTION: My question is what would the best way be to store this in one MySQL field and how would I read and write with PHP to that field? I have thought of enum() but not on the forefront of what that actually does and what it is best used for. I just know its a type of field that can have multiple items in it. Not sure if its what I need. REASON: I just want to be able to query the database with multiple category ID's and it check this field and report back if that category is present or if there are multiple present. Maybe return as a list or an array? I would like to stay away from creating multiple fields in my table for this. Have you considered separate tables? Store the categories in one table and use a third to store the item and category combination, one row per item,category combo. This is a common pattern to manage such situations. NOTE: The categories are retrieved as a number FYI. Any help/code would be greatly appreciated. But a link does just fine for me. Best Regards, Karl DeSaulniers Design Drumm http://designdrumm.com Hope your all enjoying your 2012! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Neil's solution is the best. Storing a comma separated list will involve using a LIKE search to find your categories. This will result in a full table scan and will be slow when your tables get bigger. Storing them in a join table as Neil suggested removes the need for a like search an will be faster Bastien -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Thanks guys for the responses. So.. what your saying if I understand correctly. Have the categories in one table all in separate fields. Than have a the products table. Than have a third table that stores say a product id and all the individual categories for that product in that table as separate fields associated with that product id? Am I close? Sounds like a good situation, but I didn't want to really create a new table. One product will probably have no more than 3 combinations of categories. So not sure it this is necessary. EG: Tshirts = 1 Jackets = 2 etc.. Mens = 12 Womens = 13 So lets say I want to find all the Mens Tshirts.. I was wanting one field to hold the 1, 12 hope that clarifies Karl DeSaulniers Design Drumm http://designdrumm.com Hi Karl, if you don't want to do with the third-table-solution, how about an assembler-style bit-wise OR of all categories ? constant TSHIRTS = 1 ; // 2 to the 0th power constant JACKETS = 2 ; // 2 to the 1st power constant MENS= 8 ; // 2 to the 3rd power constant WOMENS = 16 ; // 2 to the girl power :-) INSERT INTO TABLE t_myTable ( ID, categoryField) VALUES ( myNewId, TSHIRTS | MENS ) ; SELECT ID FROM t_myTable WHERE ( categoryField ( TSHIRTS | MENS )) 0 ; This assumes that your number of categories is not that big of course, as you're limited to 64 bits/categories on a modern machine. Bert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Hi Bert, Thanks for the response. I did consider that, but there may be more than 64 categories. So I am thinking that may not be best for my situation. I am actually at the same point again, but this time with the colors. I have multiple colors for each tshirt. I dont want to put all the separate colors as their own fields and there is an image associated with those colors too. I'd also like to not put those all in separate fields if I can. What's the best way to store multiple values that may change from time to time? What kind of field? IE: ('red.png', 'green.png', 'blue.png') SET() enum() blob() varchar() ??? TIA Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I am thinking of limiting the colors to 10 for now (after all there are only so many ways to die a shirt. =) and using a comma delimited list of abbreviated color names as a varchar string. Then read out that string, explode on the commas and put in an array. `pd_color` varchar(39) CHARACTER SET utf8 NOT NULL DEFAULT
Re: [PHP-DB] foreign key
On 9 August 2011 20:31, Chris Stinemetz chrisstinem...@gmail.com wrote: Okay. I am pretty new to mysql so this may seem like a ridiculous question to some people. I am trying to use a LEFT JOIN query, but the results I am finding unusual. For every record occurrence there is for the query, the results are duplicated by that amount. So if there are 3 records from the query results, then the output is 3 times what I expect.. if that makes sense. From what I have researched so far. I believe I may need to add a foreign key to build the relations between the two tables. Based on the query can any tell me the correct way of adding the foreign key if ,in fact, that is what I need? I can provide table structures and information if necessary. The query is: $posts_sql = SELECT store_subject, store_comments, store_date, store_tptest, store_by, users.user_id, users.user_name, users.first_name, users.last_name FROM stores LEFT JOIN users ON stores.store_by = users.user_id WHERE stores.store_subject = ' . mysql_real_escape_string($_GET['id']).' ORDER BY stores.store_date DESC ; The query dump is: SELECT store_subject, store_comments, store_date, store_tptest, store_by, users.user_id, users.user_name, users.first_name, users.last_name FROM stores LEFT JOIN users ON stores.store_by = users.user_id WHERE stores.store_subject = 'Noland Park Plaza 3509 S. Noland Rd' ORDER BY stores.store_date DESC Is users.user_id unique? -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] foreign key
On 9 August 2011 20:49, Chris Stinemetz chrisstinem...@gmail.com wrote: Is users.user_id unique? yes it is. What does your result look like? Hard to say what the problem is without seeing the result. -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] foreign key
On 9 August 2011 21:23, Chris Stinemetz chrisstinem...@gmail.com wrote: What does your result look like? Hard to say what the problem is without seeing the result. I am echoing the query and printing the get array just for debugging purposes, but below you can see how it is repeating its' self. Thank you So you're saying that SELECT stores.store_subject, stores.store_comments, stores.store_date, stores.store_tptest, stores.store_by, users.user_id, users.user_name, users.first_name, users.last_name FROM stores LEFT JOIN users ON stores.store_by = users.user_id WHERE stores.store_subject = 'Bella Roe 4980 Roe Blvd' ORDER BY stores.store_date DESC returns Chris Stinemetz 08-09-2011 02:08PM 600kbps-3.8mbps test Chris Stinemetz 08-09-2011 02:08PM 600kbps-3.8mbps test Chris Stinemetz 08-09-2011 02:07PM 0-250kbps test1 Chris Stinemetz 08-09-2011 02:07PM 0-250kbps test1 From the above, can't see where your problem is but something in your join is obviously not unique - whether it's the first or second table. -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: php-db foreign key
When I dump the query and run it in console I get the results I want. Not sure what I am doing wrong. Your php code had more than one query running (one inside the other). It's the outer query that runs twice, not the inner one returning double the results -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: php-db foreign key
On 9 August 2011 22:25, Chris Stinemetz chrisstinem...@gmail.com wrote: Your php code had more than one query running (one inside the other). It's the outer query that runs twice, not the inner one returning double the results Thanks Peter, Do you have any suggestions on how to fix this? Thank you, Chris Yes, debug your code and figure out why it's looping twice instead. For instance, try the other query in the mysql console. -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: php-db foreign key
On 9 August 2011 22:38, Chris Stinemetz chrisstinem...@gmail.com wrote: Yes, debug your code and figure out why it's looping twice instead. For instance, try the other query in the mysql console. Thank you! It was the first query. I put a LIMIT 1 on it and now it is working correctly. I appreciate your help! You're fixing the symptom, not the problem. Your query was returning multiple values because something is wrong with the query or your data. If you don't correct it, the problem will likely just grow bigger. -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Left Join
Maybe it's just me, but I can't see anything that would work as foreign key for you to join on - neither table seems to have a foreign key On 7 August 2011 21:53, Chris Stinemetz chrisstinem...@gmail.com wrote: I am tyring to build a query that will take the most recent stores.store_date column then join it with store_list.store_name where store_list.store_name and stores.store_subject match then return the most recent stores.store_date. I am guessing this will require some sort of join. I have been tyring to figure it out but have had no luck. Any help is greatly apprciated. Thank you. mysql select * from stores; +--+-++-+---+--+-+-- ---+ | store_id | store_subject | store_type | store_date | store_mar | store_by | store_tptest | store_comment s | +--+-++-+---+--+-+-- ---+ | 78 | Bella Roe | corporate | 2011-06-28 15:01:02 | 0 | 1 | | | | 79 | Bella | corporate | 2011-08-06 08:48:26 | 0 | 1 | 600kbps-3.8mbps | test | | 80 | Brittney~2120 | corporate | 2011-08-06 08:48:50 | 1 | 1 | 600kbps-3.8mbps | should post t o Wichita! | | 81 | Motor | premier | 2011-08-06 14:52:55 | 1 | 1 | 600kbps-3.8mbps | test | | 82 | Liberty | corporate | 2011-08-06 14:54:41 | 0 | 1 | 0-250kbps | test | | 83 | Seneca~3165 | corporate | 2011-08-06 14:58:47 | 0 | 1 | 600kbps-3.8mbps | test | | 84 | Liberty | corporate | 2011-08-06 14:59:32 | 0 | 1 | 400-600kbps | test | | 85 | Brittney~2120 | corporate | 2011-08-06 15:00:43 | 0 | 1 | 600kbps-3.8mbps | test | | 86 | Liberty | corporate | 2011-08-06 15:01:11 | 0 | 1 | 300-400kbps | test | | 87 | Brittney~2120 | corporate | 2011-08-06 15:11:06 | 0 | 1 | 250-300kbps | test | | 88 | Brittney~2120 | corporate | 2011-08-06 15:23:53 | 1 | 1 | 600kbps-3.8mbps | test | | 89 | Bella | corporate | 2011-08-06 15:31:49 | 0 | 1 | 600kbps-3.8mbps | test | | 90 | Cleartalk~10221 | premier | 2011-08-06 15:32:54 | 0 | 1 | 250-300kbps | test | | 91 | Chit | premier | 2011-08-06 15:33:50 | 1 | 1 | 250-300kbps | test | | 92 | | | 2011-08-06 15:34:21 | 2 | 1 | 300-400kbps | test | | 93 | Bella | corporate | 2011-08-07 11:26:13 | 0 | 1 | 600kbps-3.8mbps | test | +--+-++-+---+--+-+-- ---+ 16 rows in set (0.00 sec) mysql mysql select * from store_list LIMIT 15;' ++---+++-+---+ | id | market_prefix | store_name | store_type | market_name | id_market | ++---+++-+---+ | 1 | MCI | Bella Roe~4980 Roe Blvd | Corporate | Kansas City | 0 | | 2 | MCI | Cleartalk~4635 Shawnee Dr | Premier | Kansas City | 0 | | 3 | MCI | Cleartalk~3612 State Avenue | Premier | Kansas City | 0 | | 4 | MCI | Endless Wireless~1620 Central Avenue | Premier | Kansas City | 0 | | 5 | MCI | Get Wireless~840 Minnesota | Premier | Kansas City | 0 | | 6 | MCI | Ring Ring Wireless~7559 State Avenue | Premier | Kansas City | 0 | | 7 | MCI | Cleartalk~1212 Sante Fe | Premier | Kansas City | 0 | | 8 | MCI | Cleartalk~10221 W 75th Street | Premier | Kansas City | 0 | | 9 | MCI | Free Talk~10830 Shawnee Mission Pkwy | Premier | Kansas City | 0 | | 10 | MCI | Loma Vista~8712 Blue Ridge Blvd | Corporate | Kansas City | 0 | | 11 | MCI | Ring Ring Wireless~25 W. 39th Street | Premier | Kansas City | 0 | | 12 | MCI | Ring Ring Wireless~3039 Prospect Ave | Premier | Kansas City | 0 | | 13 | MCI | Ring Ring Wireless~1201 E Linwood Blvd | Premier | Kansas City | 0 | | 14 | MCI | Ring Ring
Re: [PHP-DB] mysql_num_rows == 0
On 30 May 2011 22:31, Nazish naz...@jhu.edu wrote: Hi all, I've run into a little barrier, and I'm wondering whether you have any insights. I'm entering values into a MySQL database. Before running the mysql_query, I'm checking if the value already exists (using mysql_num_rows == 0). If the value already exists in the database, the page will echo Username already exists and it won't insert the user's new value. It runs that far, and echoes the message accordingly. However, if the username is new, and does not exist in the database, the query dies (without leaving a mysql error). I tried changing the Unique IDs in the database, but it doesn't seem to be the issue. The syntax seems fine, as I used it for another similar page. Any idea why it's dying? $check = mysql_query(SELECT * FROM user WHERE user_name='$user_name') or die (Unable to query database:.mysql_error()); $numrows = mysql_num_rows($check) or die (Unable to search database:.mysql_error()); - DIES HERE when a new value is entered. no mysql_error msg. bla bla or die(more bla); is a very bad way of handling error checks. In your particular case, your foot has been shot off because mysql_num_rows will return 0 (no rows match the query) and thus the die() is executed - but of course there's no error in the query, it executed just fine, this we know already. Moral: don't use ' or die();' for anything else than hands on debugging purposes Regards Peter -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] printf
Mysql doesn't have a printf function Regards Den 2010 10 29 00:47 skrev Ethan Rosenberg eth...@earthlink.net: Dear List - I am afraid that I am missing something in a major way. mysql printf(%b %d %f %s\n, 123, 123, 123, test); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'printf(%b %d %f %s\n, 123, 123, 123, test)' at line 1 Where is my mistake? The code above is copied from the MySQL manual!! And while we are on the subject of my ignorance. I wish to write to file named Site the letter A, and read it back. I wish to write to file named Starter the number 1000 , and read it back. How do I do it? I'd better ask these questions now, so when they come up in the next hour, I will not have to bother you with another email! Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] products order
On 29 September 2010 14:00, Emiliano Boragina emiliano.borag...@gmail.com wrote: Hello, A client wants to control the order, the position of the products he load. How do I do this? You store positional data per product, defaulting to no priority in position. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to explain such a regular syntax
On 13 August 2010 08:47, win.a win@gmail.com wrote: Its was picked from drupal source code and i don't know how does it works. This is the code : preg_match('/^\[?(?:[a-z0-9-:\]_]+\.?)+$/', $host); This has got nothing to do with php or php-db - your question is on regular expressions. Try consulting http://www.regular-expressions.info/ or get a good book on the topic like Mastering Regular Expresssions by Jeffrey Friedl. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Stuck in apostrophe hell
On 3 August 2010 15:04, paul_s_john...@mnb.uscourts.gov wrote: Yes, I may have mixed up the input and output from different iterations of running it. Let me try posting this again although it may not be an issue. Once again if I enter two sequential apostrophes in the name (O''Brien) the INSERT passes right through to MySQL without an error. THE INPUT: $sql_insert_registration = sprintf(INSERT INTO Registrations ( Class_ID, prid, Registrant, Company, Phone, Email ) VALUES ( $_POST[Class_ID], $_POST[prid], '%s',. parseNull($_POST['Company'])., '$_POST[Phone]', '$_POST[Email]' ), mysql_real_escape_string($_POST['Registrant'])); echo pre$_POST['Registrant.$_POST[Registrant]./pre; echo pre.mysql_real_escape_string($_POST[Registrant])./pre; echo pre.$sql_insert_registration./pre; THE OUTPUT: Brian O'Brien Brian O\'Brien INSERT INTO Registrations ( Class_ID, prid, Registrant, Company, Phone, Email ) VALUES ( 355, 257, 'Brian O\'Brien',NULL, '612-456-5678', 'someb...@somewhere.org' ) Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Brien', 'Class registration confirmation', ' This email ' at line 16 Strangely, you have still failed to provide the input that is actually sent to mysql. Look at the error code: ... for the right syntax to use near 'Brien', 'Class registration confirmation', ' This email ' - Class registration confirmation does not appear anywhere in the output section you posted but it appears in the mysql error. I'd do as Bret suggested and turn on query logging in mysql to see what is actually received. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Stuck in apostrophe hell
On 2 August 2010 22:30, paul_s_john...@mnb.uscourts.gov wrote: Before I send the following SQL to MySQL from PHP I print it to screen. PHP chokes on it, but I can paste the exact same query from the screen directly to MySQL and it works just fine. For example: Here's the relevant PHP code: == $sql_insert_registration = sprintf(INSERT INTO Registrations ( Class_ID, prid, Registrant, Company, Phone, Email ) VALUES ( $_POST[Class_ID], $_POST[prid], '%s',. parseNull($_POST['Company'])., '$_POST[Phone]', '$_POST[Email]' ), mysql_real_escape_string($_POST['Registrant'])); echo pre.$_POST[Registrant]./pre; echo pre.mysql_real_escape_string($_POST[Registrant])./pre; echo pre.$sql_insert_registration./pre; if (!mysql_query($sql_insert_registration, $con)) { die('Error: ' . mysql_error()); == Here's the output: === INSERT INTO Registrations ( Class_ID, prid, Registrant, Company, Phone, Email ) VALUES ( 355, 257, 'Brian O\'Brien',NULL, '612-456-5678', 'paul_s_john...@mnb.uscourts.gov' ) Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Brien', 'Class registration confirmation', ' This email ' at line 16 == It's probably nothing but your mysql error does not match your php output - could you try an updated paste? Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Session start
On 14 May 2010 18:47, Barry Zimmerman barryzi...@googlemail.com wrote: I have a problem with my system, not sure how I can fix this one. A user has a log in page and takes them to different pages, now each of these pages has a check to make sure they are logged in with the following code: session_start(); if (!(isset($_SESSION['username']) $_SESSION['username'] != '')) { header (Location: login.html); exit; } So if they are not logged in it redirects them to the log in page. No problems there. Now if a user is not logged in and comes back to that page, it starts the session so giving them a session id and redirects them back to thge login page. The problem I have is I do NOT want the session to start, I need them to hit the log in page with no sessions there. I have tried all sorts but just cannot get this to work. I have tried adding this to the code. session_start(); if (!(isset($_SESSION['username']) $_SESSION['username'] != '')) { *session_destroy();* header (Location: login.html); exit; } But that did not work? Please I am stuck for ideas? Read the manual on session_destroy. Specifically, if you want to complete destroy the session, unset the session cookie as well. On a separate note: why do you care if the session has started or not? If nothing is stored in the session then there's not much difference to you. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] grabbing from multiple tables
On 30 April 2010 20:49, Karl DeSaulniers k...@designdrumm.com wrote: No, please confuse me. I need to know this stuff. @Peter thanks for that introduction to foreign keys. Since my productoptions table is based off of items in products, optionGroups and options, would I use foreign keys for this? If I read you correct, your productoptions table is basically a lookup table and as such all your fields should be foreign keys. Because, each field is a reference to another table - and it's vital to data consistency that they cannot point to a row in a table that doesn't exist. -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] grabbing from multiple tables
On 1 May 2010 10:13, Karl DeSaulniers k...@designdrumm.com wrote: What is the SQL query I can use to get an item that has two IDs? Something to the effect of... $q = SELECT OptID = '$OptID' FROM .PRODUCT_OPTIONS. WHERE ProdID = '$ProdID' AND OptGrpID='$OptGrpID'; Close but no cigar. $q = SELECT OptID FROM .PRODUCT_OPTIONS. WHERE ProdID = '$ProdID' AND OptGrpID='$OptGrpID'; I am assuming that you have escaped $ProdID and $OptGrpID :) Is this correct or am I missing something? A single group ID can have multiple option IDs set to it. A single product ID can have multiple group IDs set to it. You're looking at a many-to-many table - a row should be unique given all three IDs. Only those three IDs together should form a unique row. I am trying to single out a product option. The option has a group ID and a product ID assigned to it. I'm starting to wonder about your data model. Should options always be in groups? I'm guessing that you need one of three things: 1. Split up the product options table. A product can have some individual options and some group options - these have nothing to do with each other and you need to be able to set them without regard for each other. 2. Remove the productOptionID from the product options table. A product only has option groups, no individual options. 3. Remove the groupOptionID from the product options table. A product only has individual options, regardless of the option group these options belong to. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] grabbing from multiple tables
On 30 April 2010 12:02, Karl DeSaulniers k...@designdrumm.com wrote: Hello All, I have a product database. In that database there are several tables dealing with individual products. I am wanting to set up an editProduct and a productInfo page. For the bulk info, I am fine. For the product options, I need some guidance. I have 4 tables dealing with the options. Product table = main product table where productID is set Product Options table = table to store product option info. Where productID, optionID and OptionGroupID are stored together per ProductID. Option Groups table = general option groups EG: Size, Color. Where OptionGroupID and OptionGroupName is set. Options table = general options EG: Large, Medium, Small, Red, Blue, etc. Where OptionID and OptionName is set per OptionGroupID. Is there a way for me to call all these tables when adding a product? edit product? view product? MySQL will only allow you to insert into one table at a time. You can however update several tables at the same time - see http://dev.mysql.com/doc/refman/5.0/en/update.html You can select values form multiple tables with the SELECT syntax: SELECT table1.blah, table2.blah FROM table1, table2 WHERE table1.id = table2.foreign_key; Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] grabbing from multiple tables
On 30 April 2010 12:26, Karl DeSaulniers k...@designdrumm.com wrote: Thanks Peter. So what is the logic behind foreign keys? Why use them? Constraints. When using, for example, the InnoDB engine in MySQL, you can set foreign key fields on tables. These ensure that your record will always be bound to a proper record in the connected table - so, for instance, you won't find yourself in the situation that you have deleted a record from table1 but table2 still references the table1 record. Also, they're very useful for tying models together automatically, as you can deduce relationships between models by foreign keys, for instance (this is simplified but covers a lot of cases). Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] grabbing from multiple tables
On 30 April 2010 13:41, DZvonko Nikolov dzvo...@yahoo.com wrote: Hi, don't confuse the guy. Don't talk down to the man. He asked questions and got usable answers including links to where he could find more info. -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php mysql comparing two rows in two columns for username and passwort
On 29 April 2010 15:00, Karl DeSaulniers k...@designdrumm.com wrote: Hi, Maybe try... $benutzername = $_GET['username']; $pass = $_GET['password']; $result = SELECT * FROM usertable WHERE sqlbenutzername='$benutzername'; Don't use values from $_GET without sanitizing first. If using mysql_* functions, sanitize with mysql_real_escape_string() first. while($r = mysql_fetch_row($result)) { $dbbenutzer = $r[sqlbenutzername]; $dbpasswort = $r[sqlpasswort]; } if($benutzername == $dbbenutzer $pass == $dbpasswort){ This would work but only if you're storing passwords in the database in clear text - which is a Bad Thing and should be avoided. Hash the passwords before storing and compare with a hashed version, not the cleartext. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php mysql comparing two rows in two columns for username and passwort
On 29 April 2010 16:44, Alexander Schunk asch...@gmail.com wrote: Hello, i have it now as follows: while($dbbenutzer = mysql_fetch_assoc($sqlbenutzername)) while($dbpasswort = mysql_fetch_assoc($sqlpasswort)){ You have things very twisted. Check the code posted by Karl - you only need *ONE* row containing your *TWO* fields. mysql_fetch_assoc() retrieves that *ONE* row from a MySQL result set as an array - so having those two while statements will do you no good. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Fwd: Apache log: database shutdown
On 20 April 2010 13:37, Kamil Walas kamilwala...@gmail.com wrote: I checked. It's looks fine for me, but I paste piece of it relevant to apache log: Centrum Mon Apr 19 13:57:02 2010 INET/inet_error: send errno = 104 Centrum Mon Apr 19 13:57:02 2010 SERVER/process_packet: broken port, server exiting Centrum Mon Apr 19 14:03:49 2010 INET/inet_error: send errno = 104 Centrum Mon Apr 19 14:03:49 2010 SERVER/process_packet: broken port, server exiting Centrum Mon Apr 19 14:13:57 2010 INET/inet_error: read errno = 104 Centrum Mon Apr 19 14:30:21 2010 INET/inet_error: read errno = 104 Centrum Mon Apr 19 15:22:18 2010 INET/inet_error: send errno = 104 That hardly looks fine. Googling a bit, I found the following: http://www.firebirdfaq.org/faq140/ http://www.firebirdfaq.org/faq120/ I'd guess it's a bad connection between Apache and your DB - or Apache dropping the connection for some other reason. Regards Peter -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL vs. Session array
On 8 April 2010 00:13, 3dgtech syst...@3dgtech.com wrote: Quick question. I have an AJAX component that fires a like query for each key up event (since it's a like % query there is no indexing or caching possible). While everything works fine, I was contemplaining downloading the ~1500 rows into a session var as array, offloading the (seperate) mysql server and utilizing the (underutilized) php server. Any thoughts? You'd be better off storing stuff in memcache than session, would be my opinion - session is per user, so you wouldn't get much cache reuse out of it. -- hype WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php