Re: [PHP-DB] Re: Beginners Problem
Andy, Thanks for your comment. What I posted is only part of my code though, as the entire thing is a bit long, and with all the includes rather hard to follow unless I posted the whole file set. Above the piece I posted I have code to do slashing, and some MD5 hashing, as well enforcing string lengths. So the $password I use in the query is actually MD5 hashed already. I know I need to improve the security though, as my current code do not counter for every possible attack, so your input is much appreciated. [EMAIL PROTECTED] wrote: Could I recommend a more secure approach: 1) using two hashes to protect the data (in case the database is compromised they are both one-way hashes, and using two protects against collision attacks whereby a different password string generates the same hash as the original password) 2) escaping user input to protect against SQL injection attacks (nasty queries can get more data from the database than your original query intended, or change the query's intended functionality). Instead of: $chkuserquery = SELECT userID FROM $TB_USERS WHERE `loginID`='$loginID' AND `password`='$password' LIMIT 1; $chkuser = $db-query($chkuserquery); This example utilises the mdb2 database layer: $user_credentials = array( //these are the credentials the user supplied 'user_name' = addslashes($username), //escape username input 'user_password_md5' = md5($password), //generate hash, no injection is posisble 'user_password_sha1' = sha1($password) //due to 'scrambling' of string ); foreach ($user_credentials as $k = $v) {//build string $query_values .= $k . '=' . $db-quote(trim($v)) . ' AND '; } $query_values = '(' . substr($query_values, 0, -5) . ')'; //format string and remove AND $sql = SELECT COUNT(user_id) AS user_count FROM user WHERE $query_values; $result = $db-query($sql); //this if not only returns a row from the database query, it then checks if the user_count //field contains more than one or more results. if so, login is correct if (($row = $result-fetchRow(MDB2_FETCHMODE_ASSOC)) $row['user_count']){ $valid_login = true; //session - database etc } for this example, using 'root' and 'password', $query_values is: (user_name='root' AND user_password_md5='5f4dcc3b5aa765d61d8327deb882cf99' AND user_password_sha1='5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8') This code is identical in functionality to the previous example, except the query has no LIMIT - this is not required as it prevents the possibility of coding error handling for multiple accounts (perhaps unnecessary, excepting very secure applications). Andy -- Rene Brehmer aka Metalbunny We have nothing to fear from free speech and free information on the Internet but pop-up advertising! http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Beginners Problem
Peter Westergaard wrote: snip You should probably check out http://php.net/mysql_query for more information on this function. The difference between empty result and invalid query is significant. Once you've determined that the query is valid, you must also then check to see if it returned any rows, possibly using a function like mysql_num_rows. I usually skip the validity check and simply check for rows. But then I also write my code a little differently, which may be why it doesn't break. This is part of my login code: $chkuserquery = SELECT userID FROM $TB_USERS WHERE `loginID`='$loginID' AND `password`='$password' LIMIT 1; $chkuser = $db-query($chkuserquery); if($db-num_rows($chkuser)) { $userID = $db-result($chkuser,0); $ip = $_SERVER['REMOTE_ADDR']; // update active session $query = UPDATE $TB_SESSIONS SET `userID`='$userID',`logintime`=NOW(),`loginIP`='$ip' WHERE `sessionID`='$sessionID' LIMIT 1; $result = $db-query($query); $alert_level = 1; $alert_message = 'You are now logged in. Please remember to logout when done.'; } else { $alert_level = 3; $alert_message = 'Username and/or password incorrect'; } May deserve some elaboration, as this is just a piece of a much bigger code. $TB_USERS and $TB_SESSIONS are merely variables (constants) set earlier so that I can re-use the code more easily for different projects. $db is a simple database object that's merely a wrapper for the mysql functions. Inside $db, all the mysql functions have the or die() part set with mysql_errno() and mysql_error() which catches invalid queries. I built my own sessions system, using the database and cookies. There's always a session active, that's why this part of the code doesn't check for it. I made it this way so I can run the login/logout script at any phase of the code, without having to worry about setting sessions and cookies before it sends the headers. FWIW Rene -- Rene Brehmer aka Metalbunny We have nothing to fear from free speech and free information on the Internet but pop-up advertising! http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Delivery reports about your e-mail
Documented research indicate that on Fri, 1 Jul 2005 16:16:27 +0100, Chris Ramsay wrote: snip Please follow our instructions in order to keep your computer safe. /snip Oh jolly good, I've been waiting for those damn instructions for ages... It's starting to get really annoying with those failed mail delivery viruses ... It should help a bit if the server simply refused any post that contains attachments, or routed it to /dev/null, whatever's simplest... -- Rene Brehmer aka Metalbunny We have nothing to fear from free speech and free information on the Internet, but pop-up advertising! http://metalbunny.net/ My little mess of things... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: UPDATE command
Documented research indicate that on Mon, 20 Jun 2005 12:47:56 +0100, [EMAIL PROTECTED] wrote: The trick is your using the wrong ' instead you need ` ... go away from kl;-' and go to `-123 ;) The key above the (Tab) key, or the key next to the number 1 above the q. I hope you realise those directions only work for US keyboards (might work for UK too, never seen those), but just about every other keyboard layout it's in a different place (like on DK keyboards it's next to the backspace key, and you have to first hit, then hit space, to make it show up alone, because it's actually an accent aigu)... not sure where Ron is from, but it is something worth considering when giving keyboard directions ... Rene -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] registration form with activation code sent to user
Documented research indicate that on Thu, 16 Jun 2005 16:20:31 +0100 (BST), babu wrote: Ya joseph u r right, i am thinking of the url to be generated to be send to the user. I followed u r steps but did not understand some points. 2.) Store that string to the database somewhere where to store in database, i mean when the user clicks the activation code how to check the users identity.? I actually made this exact functionality for one of my projects a few weeks ago. I made a seperate table simply called 'confirm', that contains the userID for the user in question, the confirmationcode to match, and the expiration time for this confirmationcode, plus an auto-incremented index column. In the users table I then simply have a boolean field called 'confirmed', that's set to 0 (false) by default. When a user's email address have been confirmed, this field is changed to 1 (true). The only reason I have expiration time in this table is for cleanup purposes. 3.) Email the mail to the address specified with the url to the confirm page one must send the email manually or is there any automatic generator for email sending to users.? Read up on the mail() function. It's actually pretty simple. All you have to do is send an email, with some proper explanatory text, with a link to your confirm.php script (or what you choose to call it), either with a direct link with a get statement including the confirmation code, or with a link to the confirmation script, and then the confirmation code and instructions on how to enter it. Or at best: offer both options. Then simply have your user registration/signup code send the email whenever a user registers. this will confirm that they did in fact check thier email. how exactly, can explain little bit.? Make the code generate a unique confirmation code (this is pretty simple), and then just have your confirmation script compare the confirmation code with the one in the database. If there's a match then the user to whom you sent that particular code is confirmed. All of the code needed to do this is actually fairly simple to write. Rene -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Why does this code/query hang time out ?
... inline ... for ease: At 23:53 31/03/2005, Juffermans, Jos wrote: So far, I have found a few mistakes in your SQL: $query .= OR `variant` LIKE '%.$_GET['search'].%'; Why do you have backtick-quotes around variant? Know that backticks are used to call system commands. You also use these backticks in the ORDER BY lines. My guess is the backticks are causing your problems. Because they have to be there for MySQL to understand the query ? I sometimes have field names where part of the name is the same as a command, so without the ticks MySQL can't understand the queries. They're actually in the documentation for MySQL, so I don't get why it would cause problems. I always use them, and when I don't, I have problems. And the ORDER clause DOESN'T WORK without them... ever, when I try to do without them and have more than order clause. $query .= AND make.makeID='.$_GET['make'].'; ... $query .= AND make.makeID0; If make.makeID is a number field, why is the value in quotes for the cases where $_GET['make'] is set? Same with socket.socketID a few lines down. Because it doesn't matter, function-wise, if they're there or not for numbers. And it's easier and faster to just put them in for everything, than remembering to exclude them for numeric fields, and include them for non-numeric. Yeah, it takes MySQL a little longer to have to convert from text to number and such, but it's a microscopic amount of time. Unrelated but very important, putting form variables (either GET or POST) directly into a query is dangerous. If I create a copy of your form and fill $_GET['search'] with eg: a%';DELETE * FROM model;SELECT * FROM model WHERE something LIKE '%a Your query will be like this: SELECT .. FROM WHERE AND model.modelName LIKE '%a%';DELETE * FROM model;SELECT * FROM model WHERE something LIKE '%a OR VARIANT You can't trust incoming variables. Yeah, I know ... this code was from before I learned how to check and escape form fields though, just didn't put the error checking/correction into the code yet ... haven't worked on this thing for like a year cuz I got sick of it after putting the several hundred rows manually into the database. I wrote this script originally before I knew anything about using relational tables in SQL, I simply just changed the query code to use the relations instead, and that's when it died. That said, the query worked when being put directly into a MySQL prompt, albeit extremely slow and far beyond what's normal. But I gave up trying to fix it as I couldn't find the problem, so I dumped the entire query part of the code, and started over with new code, from another script that uses the same tables, but have never caused me any problems ... and it works now. And almost 10 times faster than before. It can pull the entire 882 rows, and generate the entire page, in 12 seconds. And all I did was use a different query as basis, write completely new query generating code from scratch, and add some functionality to the form ... I haven't added the LIKE part to the new code, but I've changed the field select boxes to multi-select instead of single-select, and the order picking now lets you choose 3 orders, instead of just 1, so the code's a bit different now. I know it looks like it does some double-checking of the same thing, but it's to be damn sure it doesn't run the code parts unless it has to. And the code works, and much, much faster than the old one. In fact it takes it longer to ship the data over my network than it takes the server to run the script. I don't get why it's so much faster now, as there's very little difference in the query, but it does, and it's all I really wanted. It still needs some fine-tuning to handle the addslashes and such, but I haven't gotten to it yet. // array holding possible fields for sorting. It's used in 3 drop-downs in the query form. $arrsort = array('Make','Model','Variant','Clock','Multiplier','FSB','FSBx','L1','L2','L3','Vcore','Vcache','Socket/Slot'); if ($_POST['do'] == 'search') { $filterwhere = ''; if (! empty($_POST['make'])) { if (is_array($_POST['make'])) { $arrmake = array_values($_POST['make']); if (! in_array('all',$arrmake)) { if (count($arrmake) 1) { $filterwhere = cpu_maker.makeID IN('.implode(',',$arrmake).') AND; } else { $filterwhere = cpu_maker.makeID='{$arrmake[0]}' AND; } } } } if (! empty($_POST['socket'])) { if (is_array($_POST['socket'])) { $arrsocket = array_values($_POST['socket']); if (! in_array('all',$arrsocket)) { if (count($arrsocket) 1) { $filterwhere .= cpu_socket.socketID IN('.implode(',',$arrsocket).') AND; } else { $filterwhere .= cpu_socket.socketID='{$arrsocket[0]}' AND; } } } } $filterorder = ''; for ($i = 0; $i 3; $i++) { $num = $i+1; $sort = 'sort'.$num;
[PHP-DB] Why does this code/query hang time out ?
Hi gang My CPU database (http://metalbunny.net/computers/cpudb.php) - still a work in progress - used to be in 1 table, but for several reasons I've decided it's better to split the data into multiple related tables. Obviously this means I have to rewrite the query tool for it, and that's where my problem lies. I've included the code I'm working with below, it's a little different than the one for the above URL, as it uses my new faster templates and relies more on the database than the old code did. All the DB connect stuff is in the template, and I use MySQL. The new version isn't available online, it's only on my local development server. I'm pretty sure it's simply a coding problem, but for the life of me I can't find anything that looks wrong ... but then I've been staring at it for hours... My problem came after I tried making it possible to pick 'all' as a search option in make model, and now, nomatter whether it's set to all or not, and nomatter what's in the search field, the code stalls and hangs ... and in the last tries, Firefox ended up closing down ... I tried putting athlon in the search box, and just leave everything on default, and the generated query looks like this: SELECT make.makeID,makeName,model.modelID,modelName,fsb2,socket.socketID,socketName,cpuID,variant,clock,multi,fsb,l1,l2,l3,vcore,vcache FROM cpu_maker AS make,cpu_model AS model,cpu_socket AS socket,cpu_cpus AS cpu WHERE make.makeID=model.makeID AND socket.socketID=model.socketID AND cpu.modelID=model.modelID AND model.modelName LIKE '%athlon%' OR `variant` LIKE '%athlon%' AND make.makeID0 AND socket.socketID0 Leaving the search box empty produces no result - it's an unintended leftover from the old code that I haven't found a good way to get around yet. The code I'm working on looks like this (beware, it's rather long): ?php // load dependencies require('../include/sql.php'); // set data for template $section = 'tools'; $style2 = 'cputables.css'; $title = 'CPU Database'; $menu = true; // begin to build query string $query = 'none'; $basequery = 'SELECT make.makeID,makeName,model.modelID,modelName,fsb2,socket.socketID,socketName,cpuID,variant,clock,multi,fsb,l1,l2,l3,vcore,vcache FROM cpu_maker AS make,cpu_model AS model,cpu_socket AS socket,cpu_cpus AS cpu WHERE make.makeID=model.makeID AND socket.socketID=model.socketID AND cpu.modelID=model.modelID'; // part 1, search parameters if (! empty($_GET['search'])) { $query = $basequery; $setorder = true; $query .= AND model.modelName LIKE '%.$_GET['search'].%'; $query .= OR `variant` LIKE '%.$_GET['search'].%'; if ($_GET['make'] != 'all') { $query .= AND make.makeID='.$_GET['make'].'; } else { $query .= AND make.makeID0; } if ($_GET['socket'] != 'all') { $query .= AND socket.socketID='.$_GET['socket'].'; } else { $query .= AND socket.socketID0; } $linkquery = substr($_SERVER['QUERY_STRING'],0,strpos($_SERVER['QUERY_STRING'], 'order')); } // part 2, sort order if ($setorder) { switch ($_GET['order']) { case 'socket': $query .= ' ORDER BY `socketName`'; break; case 'vcache': $query .= ' ORDER BY `vcache`'; break; case 'vcore': $query .= ' ORDER BY `vcore`'; break; case 'l2': $query .= ' ORDER BY `l2`'; break; case 'l1': $query .= ' ORDER BY `l1`'; break; case 'fsb': $query .= ' ORDER BY `fsb`'; break; case 'multi': $query .= ' ORDER BY `multi`'; break; case 'clock': $query .= ' ORDER BY `clock`'; break; case 'variant': $query .= ' ORDER BY `variant`'; break; case 'model': $query .= ' ORDER BY `modelName`'; break; case 'make': default: $query .= ' ORDER BY `makeName`'; break; } // part 2b, asc/desc switch ($_GET['ad']) { case 'd': $query .= ' DESC'; break; case 'a': default: $query .= ' ASC'; break; } } // include template require('../include/temptop2.php'); if ($query != 'none') { $result = mysql_query($query) or die('Unable to do querybr'.mysql_error().'brbr'.$query.'br'); $numrows = mysql_num_rows($result); if (! isset($numrows)) { $numrows = 0; } } ? p align=left class=txtThis is a beta version of this database. As such some minor errors in the operation can be expected, and all features may not work equally reliable. The detailed information for when you click makes or model have not yet been made for all CPUs, but this work is underway. The database is not entirely up to date, or complete, but as time permits and information becomes available, it will be updated with the missing information.br br If you find any errors, or would like some additional functionality, plz use the a href=../contact.php class=linkcontact form/a to message me./p div align=center form action=?php echo($_SERVER['PHP_SELF']); ? method=get table width=560
[PHP-DB] Looking for more optimal way to do multiple similar inserts at once
Hi gang I'm in need of, if at all possible, cutting down the processing time for sending messages to multiple recipients with my insite messenger. The current version runs 1 query to get the names of all recipients, then 1 query per recipient, and then 1 query to deliver a copy to the sender. The code in question is below. $userID and $username is the current user, and are set by the login system much further up. $_POST['rcvID'] is a multi-select select box where the user can choose which users to send the message to. The rest should be pretty obvious. I use a couple flags on the messages so the system can tell received and sent messages apart in the message boxes. // add slashes as needed if (! get_magic_quotes_gpc()) { $subject = addslashes($_POST['subject']); $message = addslashes($_POST['message']); } else { $subject = $_POST['subject']; $message = $_POST['message']; } // get names of recipients if (count($_POST['rcvID']) 1) { $where = `userID` IN('.implode(',',$_POST['rcvID']).'); } else { $where = `userID`='.$_POST['rcvID'][0].'; } $rcvquery = SELECT userID,nickname FROM hf_users WHERE $where; $rcvdata = mysql_query($rcvquery) or die('Unable to get recipient namesbr'.mysql_error()); // send messages while ($rcv = mysql_fetch_array($rcvdata)) { $msgquery = INSERT INTO hf_privmessages (`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`priority`,`date`,`subject`,`message`,`folder`) VALUES ('.$rcv['userID'].','$userID','.$rcv['userID'].','$username','.$rcv['nickname'].','.$_POST['priority'].',NOW(),'$subject','$message','inbox'); $result = mysql_query($msgquery) or die('Unable to insert messagebr'.mysql_error()); $to_name = $rcv['nickname']; } // create sent copy if (count($_POST['rcvID']) 1) { $toID = implode(',',$_POST['rcvID']); $to_name = 'multiple'; } else { $toID = $_POST['rcvID'][0]; } $msgquery = INSERT INTO hf_privmessages (`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`is_read`,`is_sent`,`priority`,`date`,`subject`,`message`,`folder`) VALUES ('$userID','$userID','$toID','$username','$to_name','1','1','.$_POST['priority'].',NOW(),'$subject','$message','sent'); $result = mysql_query($msgquery) or die('Unable to insert messagebr'.mysql_error()); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to find
1. learn the basics of arrays 2. RTFM At 12:27 11-11-2004, peppe wrote: Hi , I have numbers in my array 0 0 0 1 1 1 1 1 1 1 1 0 0 0 0 or 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 or 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 how can I find the first 1 and the last is there a function for it -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Looking for more optimal way to do multiple similar inserts at
At 13:42 11-11-2004, Sebastian Mendel wrote: // send Messages $sql = ' INSERT INTO `hf_privmessages` (`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`priority`,`date`,`subject`,`message`,`folder`) SELECT `userID` AS `ownerID`, ' . (int) $userID . 'AS `fromID`, `userID` AS `toID`, ' . $username . ' AS `from_name`, `nickname` AS `to_name`, ' . $_POST['priority'] . ' AS `priority`, NOW()AS `date`, ' . $subject . ' AS `subject`, ' . $message . ' AS `message`, inbox AS `folder` WHERE `userID` IN (' . implode( ', ', $_POST['rcvID'] ) . ') '; mysql_query ... ; // place copy $toID = implode(',',$_POST['rcvID']); if ( count($_POST['rcvID']) 1 ) { $to_name = 'multiple'; } else { // get recipient name $to_name = 'multiple'; } $msgquery = INSERT INTO hf_privmessages (`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`is_read`,`is_sent`,`priority`,`date`,`subject`,`message`,`folder`) VALUES ('$userID','$userID','$toID','$username','$to_name','1','1','.$_POST['priority'].',NOW(),'$subject','$message','sent'); $result = mysql_query($msgquery) or die('Unable to insert messagebr'.mysql_error()); your databes lags normalization!!! My lack of normalization is open for debate, the tables are designed this way to suit the flexibility I need ... the reason I have the ownerID in the table is simply to make the code easier for me to read. I do know it's not strictly needed because I can find the right owners by using the read and sent flags, but it made the code harder for me to read when I did it like that. Because this project is nowhere near finished, I need to keep the code a little easier to read, at the cost of a little performance. I'm aware that having the to/from names with the messages don't look immediately like a good idea, because it adds apparently superflous data to the table, but it serves a specific purpose. If I didn't have the names with the messages, then I'd have to use joins to get those in when pulling the messages from the database. That in itself isn't the problem. The problem comes with deleting users. If a user is deleted, there's no information to attach to the corresponding ID in the message, and thus the join would omit any messages sent to/from users no longer in the system, and in the case of userID recycling, there's the risk of it appending the wrong username to the message. The sheer number and size of the fields in the userprofiles in this system makes it unpractical to simply mark them as deleted when deleting them. It would mean a serious waste of diskspace that goes beyond what the little lack of normalization in this one table causes. I have the username fallback fields in 3 of 14 tables, with the planned design that will eventually become around 20-22 total tables, because of alot of needed cross-linking between tables. It goes back to the practicality of not keeping user profiles in the system for any other purpose than making joins work. I'm by far not an expert on neither PHP nor SQL or database design, and never claimed to be. I learned to program when I was 8, in Basic, and learned about databases by fooling around with MS Access. It's been 20 years, and till last fall I've mostly never touched creating or designing databases at all cuz I've mostly worked with the HTML side of things, and otherwise only done coding for databases made by others, where I've had no say in how the database was organized. Everything I do these days is about learning how to push the limits of what the code and the database allows. And to learn how to get the functionality I want in the most efficient and practical way. Slandering my work will not do me any good. If you wanna come with constructive criticism that's fine, but have you ever considered that there are reasons for everything ? If you don't know the reason for the design choice, or the whole picture, any criticism you make will always and only be objective, and not constructive. But most the entire database have fields cut back to make the normalization as great as possible. I'm still largely working on getting all the functionality working primarily, and thus I'm still doing some experimentation with the database design, as in what fields I need and don't need to do what I need to do. The 3 tables that aren't fully normalized contain posts and messages in various forms. For the simple reason of being able to display those posts/messages, and do so with the correct original username, I slack a little on the normalization to make that work. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Looking for more optimal way to do multiple similar inserts at
yeah, I noticed that ... haven't had a chance to check for certain, but I believe the server I have to work with is 3.23 ... and since my space is rented, I don't have alot of say in upgrading ... although it might be possible to get them to do it ... their PHPMyAdmin is really old too ... :-s Rene At 16:17 11-11-2004, Norland, Martin wrote: Please note that his solution uses a subselect - and your version of mysql may not support subselects. Subselects were introduced (I believe) in Mysql 4.1 - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -Original Message- From: Sebastian Mendel [mailto:[EMAIL PROTECTED] Sent: Thursday, November 11, 2004 6:42 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: Looking for more optimal way to do multiple similar inserts at // send Messages $sql = ' INSERT INTO `hf_privmessages` (`ownerID`,`fromID`,`toID`,`from_name`,`to_name`,`priority`,`date`,`subj ect`,`message`,`folder`) SELECT `userID` AS `ownerID`, ' . (int) $userID . 'AS `fromID`, `userID` AS `toID`, ' . $username . ' AS `from_name`, `nickname` AS `to_name`, ' . $_POST['priority'] . ' AS `priority`, NOW()AS `date`, ' . $subject . ' AS `subject`, ' . $message . ' AS `message`, inbox AS `folder` WHERE `userID` IN (' . implode( ', ', $_POST['rcvID'] ) . ') '; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Book recommendation for using MySQL w. PHP
Thanks Rich I'm aware of that problem, but without getting my hands on the book it's a little hard to see if it covers my needs. It's the same with my PHP books ... everyone told me to get Sterling Hughes' PHP Developer's Cookbook, but beyond the chapter on constructing and using objects, and a little bit in the regular expressions part, I've not really had any use of that book at all - it simply does not cover the kinda stuff I work with... It was cheap enough though, so I'm not complaining, but alot of the things it covers aren't done deep enough for my needs. I'm sure there are others that could find more use of it, but for me it's limited... Undoubtfully the same with books on other parts of the coding ... most my C++ books are pretty useless, same with the ones on alot of other things. The computing books market seems to be filled with more crummy books than good ones Rene At 13:56 04-11-2004, Hutchins, Richard wrote: Rene, My _opinion_ only here, but I own the Welling and Thompson book and used it when I was starting out with PHP and MySQL but don't use it much anymore. I have found much better information and practical advice in the MySQL manual and in the MySQL forums. I'm not ragging on the W T book by any means; it was a good book for beginning and intermediate needs. But you may find it deficient when looking for advanced, bleeding edge stuff. And one man's advanced may be another man's intermediate so take that into consideration too. Unfortunately, I don't have any specific recommendations for an advanced MySQL book, but I would strongly suggest you investigate other books in addition to the Welling and Thompson book. Please let us know what you (or your girlfriend) end up buying since I'm sure there are others that are facing the same decision. Rich -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Book recommendation for using MySQL w. PHP
hi gang Any recommendations for a book that goes into depth with the SQL language and specifically MySQL's variant, and especially using that with PHP I don't need a beginner's guide, cuz I've gotten far beyond that on my own, but I need some help at figuring out the more complex possibilities, and how to put them into practical use, with both the PHP commands and the SQL language itself. I'm primarily using MySQL, and want to learn that properly, before I start moving on to PostGres and the others... Any recommendation would be beneficial, thanks alot in advanced :). Christmas is coming up, and my girl wants to know what to give me ... heh :P Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Book recommendation for using MySQL w. PHP
Thanks ... the reviews for that one are pretty promising :p Rene At 04:37 04-11-2004, David Orlovich wrote: I'd recommend Welling and Thomson PHP and MySQL Web Development. It's nice and thick and a new (3rd) edition has just come out. David. -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Book recommendation for using MySQL w. PHP
At 04:42 04-11-2004, Manuel Lemos wrote: Hello, On 11/04/2004 01:22 AM, -{ Rene Brehmer }- wrote: Any recommendations for a book that goes into depth with the SQL language and specifically MySQL's variant, and especially using that with PHP I don't need a beginner's guide, cuz I've gotten far beyond that on my own, but I need some help at figuring out the more complex possibilities, and how to put them into practical use, with both the PHP commands and the SQL language itself. I'm primarily using MySQL, and want to learn that properly, before I start moving on to PostGres and the others... Any recommendation would be beneficial, thanks alot in advanced :). Christmas is coming up, and my girl wants to know what to give me ... heh :P You may want to take a look here for some of the books like you want including reviews: http://www.phpclasses.org/reviews/ Heh, thanks ... nice list of reviews :) Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Database Design Recommendations
through the database itself, and as a policy, I don't allow anyone except me within 6 feet of the running system database. Securing sensitive information goes beyond just having a fancy login system, you'll need to control access to the system that stores the information as well. Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Using an array(-ish) in SQL queries
X-posted to MySQL and PHP DB Hi gang Task at hand: deleting or selecting (same difference) several numbers of records using only 1 query. My first version simply looped through all the ticked off IDs and ran a single query for each delete routine. I've still not suceeded in getting the delete queries to work on multiple tables at once, despite the column names being the same. But besides this: My current version generates, for multi-select cases, queries like this: DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR `ID`='5' OR `ID`='6' or similar with the SELECT statement. On some occasions this can result in a very large amount of OR statements, like for 50 IDs totally. I've been reading through the MySQL manual and the comments in the select and delete parts, but cannot seem to find any mentioning of an easier way to do this. Or it's been deluting me cuz English is my second language, so the MySQL manual doesn't always make much sense to me. I'm looking for something like passing on an array (as comma-seperated-list maybe), and then just do statements like: DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6) Did I totally miss that part of the manual, or is it just not possible with MySQL ? Now, for my script it doesn't really matter much which approach to use, but was more thinking performance wise it ought to be faster and less taxing for the server to parse an SQL statement that's closer to table structure, rather than the OR statements that has to be transformed first. Sorry if I'm just a blind mouse that can't seem to find things in the MySQL manual. It's not really my best friend... TIA Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Using an array(-ish) in SQL queries
At 02:37 03-11-2004, Jennifer Goodie wrote: [snip] My current version generates, for multi-select cases, queries like this: DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR `ID`='5' OR `ID`='6' or similar with the SELECT statement. [snip lots of stuff] DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6) use IN http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1268 If you know all the values in the array are escaped and safe you can just use implode() to make the list for IN $string = implode(',',$array); $sql = SELECT FROM $table WHERE col_name IN('$string'); Notice I added single quotes around the string, that is because they will be missing since implode only sticks the string between array elements. However, you'd need a join that makes sense for a multi-table delete. I don't know if it will work with a union, I have never tried, maybe somone else will chime in. thanks a whole bunch ... can't believe how close I was ... and I couldn't even find it in the manual :-/ and for some reason I've never thought of implode :-s ... anyways, live and learn ... thanks :) Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Using an array(-ish) in SQL queries
so close heh ... thanks mate :) and whether they're to actually be deleted, that depends on the application ... I have a few different where I use it. Rene At 02:13 03-11-2004, Bastien Koert wrote: DELETE FROM the_table WHERE `ID` IN(1,2,3,4,5,6) will work just fine. The trick is to be sure that those records indeed are to be deleted. I prefer to mark the record as deleted for a time before permanent deletion. That way its recoverable should something really bad happen. bastien -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How do I reverse-finding root entry in tree-structure ?
Hi gang This is possibly insanely simple, but I've been coding on it for the past 11 hours, so my head's gone a little fuzzy. been through the MySQL manual, but it's not really agreeing with me on finding anything useful... I've got a table of categories, organised in a tree structure: table wp_categories catID int(10) UNSIGNED parentID int(10) UNSIGNED rootID int(10) UNSIGNED level tinyint(3) UNSIGNED cat_title varchar(255) cat_desc text catID being the primary key, parentID the parent's ID, and rootID the ID of the root category of the branch this category belongs to. Level is simply a number as to how deep in the tree the given category is. I only use it to make it simpler to build the graphics. Here comes the problem: I need to be able to build visually the branch from root to a given folder, using only the given folder's ID as starting point. That is, traversing backwards up the branch from any given folder, and reattaching the branch till the root is reached. Building the entire tree from root down is easy enough, but for some reason I've got a brainfart as to how I can go backwards. That said, I know I could probably do this using recursive SQL statements, but I'd rather use as little talking with the DB as possible. The goal is to do this one task with as few SQL calls as entirely possible, preferably 1, 2 at most... since I found building arrays and traversing them is much faster than SQL calls... the root ID is 0, so any category in root, will have parentID of 0, and rootID of 0. I figure using the rootID to just pull the relevant branch might be part of it, but my problem comes with reattaching the branch correctly, and finding the root folder and stick the branch to it ... and doing all of this in reverse since that's what I need for the visual part... Using MySQL btw ... TIA Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL max records
How many records it can hold before becoming too slow for practical use depends entirely of the hardware that makes up the server. Current versions of MySQL has a finite limit of 2^64 records per table, but how many billion records you can shove into it before you start seeing performance issues depends on the RAM size, the RAM/CPU roundtrip speed, and the pure processing power of the CPUs, as well as the overall load of the server. Obviously dedicated DB servers/clusters will be able to handle alot higher record counts than mixed-purpose servers. Rene At 01:32 16-10-2004, ApexEleven wrote: I tried a little research on the mysql list but didn't find what I was looking for.What is the limit of a MySQL database? How many hundreds of thousands of records can a database hold before it gets too sluggish to work on a production server? thanks, -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to optimize select of random record in DB ?
I made this code to pick a random record from a changeable number of records in a given table. I'm just curious if any of the more awake coders out there can see a way to optimize this for better performance, since there's several other DB queries on the same page. $records = mysql_query(SELECT COUNT(*) AS count FROM persons) or die('Unable to get record countbr'.mysql_error()); $totalcount = mysql_result($records,0) - 1; $rndrecord = rand(0,$totalcount); $personquery = mysql_query(SELECT personID FROM persons LIMIT $rndrecord,1) or die('Unable to get random recordbr'.mysql_error()); $personID = mysql_result($personquery,0); -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] unsubscribe me
it says very nicely in the headers: list-help: mailto:[EMAIL PROTECTED] list-unsubscribe: mailto:[EMAIL PROTECTED] list-post: mailto:[EMAIL PROTECTED] At 16:23 22-09-2004, Joseph Crawford wrote: also check john's signiture on how to unsubscribe ;) On Wed, 22 Sep 2004 10:23:52 -0400, John Holmes [EMAIL PROTECTED] wrote: From: CJ Koh [EMAIL PROTECTED] unsubscribe me -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL: Problem optimizing multi-table row deletion ... errors out :(
hi gang I'm working on optimizing the SQL for my forum system, but have run into a problem trying to optimize the function for deleting a thread with accompanying stats data and posts from the database using 1 query. The current function, that works, looks like this: // delete all traces of a thread function delThread($threadID) { $result = mysql_query(DELETE FROM hf_threads WHERE `threadID`='$threadID'); $result = mysql_query(DELETE FROM hf_thread_stats WHERE `threadID`='$threadID'); $result = mysql_query(DELETE FROM hf_posts WHERE `threadID`='$threadID'); } I went through the MySQL manual, to try and find a way to optimize this, and came up with this variant: // delete all traces of a thread function delThread($threadID) { $result = mysql_query(DELETE FROM hf_threads,hf_thread_stats,hf_posts WHERE `threadID`='$threadID') or die('Unable to delete threadbr'.mysql_error()); } only it doesn't work at all ... the error output says this: Unable to delete thread You have an error in your SQL syntax near 'hf_thread_stats,hf_posts WHERE `threadID`='85'' at line 1 (Obviously I attempted to delete the thread with ID 85 here) I'm dealing with MySQL 3.23.56-log and PHP 4.3.0 ... Any ideas for optimizing this will be greatly appreciated, esp since I'm not all that great with the more complex SQL syntax... TIA Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Handling missing rows on joins
Working with PHP 4.3.0 and MySQL 4.0.14 (but code MUST be compatible with 3.23.56). For my forums, I've got this 4-way join to load (and output) the posts with info on the poster: $fullthread = SELECT postID,hf_posts.userID,name,time,post,edit,nickname,joined,location,posts,levelname FROM hf_posts,hf_users,hf_user_stats,hf_levels WHERE hf_posts.threadID='$threadID' AND hf_posts.userID=hf_users.userID AND hf_user_stats.userID=hf_users.userID AND hf_levels.levelID=hf_users.levelID ORDER BY hf_posts.time ASC; I'm not gonna paste the entire table structure, cuz it comes to rather many rows those 4 tables make up. But it's pretty basic really: hf_posts contain the posts in the forum. hf_users contains the static userinfo = the user's profile, level assignment and a few control values. hf_user_stats contains the dynamic user info - number of posts threads, last login and logout, last IP, and things like that. hf_levels contains information and general perms for the user levels. hf_posts has postID as primary key, and threadID and userID as foreign keys. hf_users has userID as primary key, and levelID as foreign key. hf_user_stats is basically an extension of hf_users, and thus only have userID as primary key, but it also functions as foreign key. hf_levels has levelID as primary key, and no foreign keys (it's a top-level table in the relations). The way it's made, when a post is made, the poster's userID and displayname is stored in hf_posts under 'userID' and 'name' respectively. But the poster's current displayname is always in hf_users.nickname. Posts by unregistered users (or those not logged in) are stored with userID 1. Since the system allows unregistered users to post, I've got a guest user implanted into the hf_users table on ID 1, just so the join won't fail to display those posts. When the script outputs the posts, it checks the userID of the posts. If the userID = 1, then it prints the displayname stored with the post, and skips the userdata for that post, otherwise it uses the displayname from the users table. But here comes the problem: If a user is deleted, the records from hf_users and hf_user_stats will be gone. On the join, this means that any posts made by the deleted users will not be included, and thus not displayed -- they become dangling posts in the database ... I haven't actually written the code to delete users yet, but my current idea for a workaround is that on deleting the users, changing all their posts to userID 1 (the system guest level). This would atleast let the posts be displayed, although make those posts display as posted by a guest, and because the displayname is stored with the post, it would not list as Visitor/guest. What I have problem with is figuring out whether this is a kludge or the only way to do it. Or is there a better way to make the join so it will include the posts even if it cannot find anything to join with in hf_users and hf_user_stats ??? I know it looks like deleting levels will cause problems as well, but the code for that has been made so deleting a level will move all users assigned to that level to a different one. Any suggestions for handling this better would be appreciated ... TIA Rene -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Handling missing rows on joins
it. Or is there a better way to make the join so it will RB include the posts even if it cannot find anything to join with in hf_users RB and hf_user_stats ??? RB I know it looks like deleting levels will cause problems as well, but the RB code for that has been made so deleting a level will move all users assigned RB to that level to a different one. RB Any suggestions for handling this better would be appreciated ... -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to write file store file-name to DB...
Why not just save the information to the database and generate the HTML when needed ??? In the long run, this would mean alot less disk space needed... Rene At 02:15 19-04-2004, you wrote: Hi, I want to save pages that show records for each user's identity, to HTML files then add the file-name to database.. Each page contain information of certain user.. How to save page to HTML file with PHP...?? Nicholas Sk2 -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Mail() - Preposterous Accusation
I think where the bounces goes is really a matter of mailserver configuration more than PHP itself... not sure about that really though ... I only use the mail() function to send mail from the website to the webmaster ... so that I don't have to expose the actual email addy on the site itself... so never had a bounce... Rene At 00:25 09-04-2004, Ryan Jameson (USA) wrote: He says later in his email that: The reason I mention this is because any email delivery failures will not be sent back to you, but to our servers (due to the way that PHP writes the email headers when using the mail() command). ... Is there a way to get the bounces to go to the reply to address? I've never really cared to, but now that he mentions it ... It would be nice. Ryan -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mail() - Preposterous Accusation
At 03:02 09-04-2004, Jochem Maas wrote: Justin Patrin wrote: Sounds alot more like advertising than sanity ... there's not much trickery in using the mail() function ... only if you want to attach files can it get a bit tricky (encoding the file and inserting the result), but in reality, there's not much in using that function properly ... you'll find that phpmailer is a quite nice wrapper for the mail function - it presents 1 clear object interface and handles all the mundane tasks of CC, BCC, Attachments, Encoding, MIME etc. I have been using it for quite sometime now. just drop 2 files into your project include/class directory and your pretty much off using a simple Mailer object. I think advertising is a bit harsh considering it free software. I usually interested in what people think are good tools/classes, even if its the writer (is that even the case here?). let the code speak for itself. I meant more the wording in that paragraph ... to me it sounds like someone that's given up figuring out how to do it himself. I've always learned, in the classic programming, that you need to learn how it's done, before you start using others code to do the work. In my oppinion, if you use a module you don't fully understand how works, or why it works how it does, then you'll never be able to take full advantage of it, or know what to do if/when errors occurs. incitement_to_riot code re-use is good right? which is why ideas like PEAR a good for everyone. and in the interest of starting a flame war (which there seems to be a bit of going around lately) I'll say that IMO most of PEAR (incl. the core is bloatware) and that PHP5 will make alot of it redundant, they should tear down all but the package manager and a few the gems and start again. /incitement_to_riot Actually ... in school (Advanced Computer Studies = programming/system development) we basically learn that copy/paste is the most important function for a programmer ... despite the crudeness of that statement, it's just a matter of saying that we shouldn't rewrite everything everytime, but try to reuse as much of the code as entirely possible ... so yes, code reuse is good, but if you don't understand the code you reuse, you're not really benefitting much of it (other than ending in the same category as script-kiddies)... Rene -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mail() - Preposterous Accusation
At 00:01 08-04-2004, Ryan Jameson (USA) wrote: I know this isn't technically DB related but this is the list that I use. I'd just like to know if anyone else thinks the below statement is accurate. If it is, why hasn't the mail function been modified in the more recent builds? I've been using PHP since it was invented never had a problem with mail. Also note that your form page is currently using the PHP mail() function, which doesn't work very well anymore. You would be best advised to use another solution (one example is PHPMailer - http://phpmailer.sf.net/) for doing PHP script-based email delivery. Sounds alot more like advertising than sanity ... there's not much trickery in using the mail() function ... only if you want to attach files can it get a bit tricky (encoding the file and inserting the result), but in reality, there's not much in using that function properly ... Rene -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Overhead on mylsam type mysql database
Do you have an auto-increment key field that's used up ??? ... As in no more free numbers in its positive range ??? Rene At 10:35 03-04-2004, you wrote: Hi all I get a overhead in my mysql database, records are not getting inserted, and it looks like, if i delete one another can be inserted. This is what i see.Data 362,692 Bytes Index 5,120 Bytes Overhead 22,568 Bytes Effective 345,244 Bytes Total 367,812 Bytes Your help will be very much appreciated. Thanks Piet -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Tree structure - how to show only current branch ??
At 20:03 30-03-2004, you wrote: Well, I did a similar system for my CMS sollution. If I understand you correct you want to sort the main branch, keeping the subs. Not really ... I want to be able to expand the current sub to the current sub's level, list the subfolders contained within the current sub, and have it not expand any other subs of the root... and then still list the entire root folder structure before and after ... my main problem is really finding an easy way to locate the root folder to start branching, with using as few queries as possible ... The sorting is really completely irrelevant in this connection, it's more a matter of locating the right branches to expand... I've not looked at it since I did the first test ... been busy with other things ... but was considering doing a series of reverse recursive SQL pulls, where I start with the requested folder's ID, put that in an array, then find the parent of that folder, put that into the same array, find the parent of that folder, put that ID into the array, and keep doing that till I reach the root folder. Then load the root folders and print them out till I reach the root folder in the array, and then start branching out from there, listing the contents of the subfolders, and branching out the ones in the array... The reason I don't like to keep the level of the folder in the database is that I want to be able to move any and all folders around across levels in the database ... and keep their subfolders attached ... Sofar I'm a bit blank in regards to this especially because I'm doing it to figure out how to do it, but don't really have anything specific I want to use it for yet, that requires more than 2 level of folders... Rene -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to get only 2nd row in result set ???
I couldn't find anything like this from my archive searches ... so here goes: I've got this query: $levels = mysql_query(SELECT levelID from hf_levels ORDER BY levelorder DESC LIMIT 2) or die('Unable to get levelsbr'.mysql_error()); but I can't figure out how to write the rest of the code ... I need to skip the first row and get the data from the 2nd row... but because there's an optional up to 255 possible rows in this table, and the order of these are determined by a user customizable field called levelorder ... so I can't just pick the specific numrows - 1 ... :-/ I know this is a kludge to do it this way ... but it's what I need for now ... later I'll get to do it a more brilliant way... TIA Rene -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums @ http://forums.metalbunny.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Tree structure - how to show only current branch ??
Eh ... now, I dunno what part you missed ... but that's JavaScript, and I'm working in PHP ... why would I care what's been done in JS ??? That won't get me much further with what I'm trying to do... Not to mention that I hate using others code, and never have sofar, then it will take about as long to convert that to something workable in PHP, as it will figuring out how to do it in PHP Rene At 14:15 30-03-2004, you wrote: Already been done: http://www.destroydrop.com/javascripts/tree/ Mark -- Rene Brehmer aka Metalbunny ~ If you don't like what I have to say ... don't read it ~ http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] What's the difference here????
Hi gang I ran into a minor, but weird problem yesterday ... Query one: $catpermsquery = mysql_query(SELECT cat_view FROM hf_category_perms WHERE `catID`='$catID' AND `levelID`='$levelID' LIMIT 1) or die('Could not get cat perms'.mysql_error()); $catperms = mysql_fetch_array($catpermsquery); $cat_view = $catperms['cat_view']; $perms = mysql_fetch_array(mysql_query(SELECT may_view FROM hf_level_perms WHERE `forumID`='$forumID' AND `levelID`='$levelID' LIMIT 1)) or die('Unable to get permsbr'.mysql_error()); $may_view = $perms['may_view']; Both queries are on the same page ... the first one makes the script determine whether or not to run the query that generates the loop where the second query is inside ... If I write the first query like the second one: $catperms = mysql_fetch_array(mysql_query(SELECT cat_view FROM hf_category_perms WHERE `catID`='$catID' AND `levelID`='$levelID' LIMIT 1)) or die('Could not get cat perms'.mysql_error()); Then all it displays is my error message: Could not get cat perms ... and no MySQL error message. I cut it up like above, and it works fine. All I really wonder about is WHY it behaves like this in one query, but not the other the two tables that are used in here have similar structure, one primary key, ID, that's merely an auto-increment that I don't use for anything but the required indexing, then catID in the first table, forumID in the second, and levelID in both. For each combo of levelID and catID/forumID there's 1 row in each table. The only structural difference is that the second table has 10 more fields than the first one... This is the only time I've had a problem with putting mysql_fetch_array(mysql_query()) inside each other ... some of the queries I naturally have to split because I need to check if there's any result returned from the query ... but with these perms query the data they look for will always exist (unless MySQL makes a mistake and dumps a row without being told to), because they're only run inside other queries that look for the respective catID/forumID these uses, so they won't be called unless that particular category/forum exists. So there shouldn't be a problem combining the two statements into 1 ... (other than it not exactly being textbook coding :p ) TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Looking for simpler way to do this...
I'm working with PHP 4.3.0 w. MySQL 4.0.14b on Windows XP I've written my own (pretty advanced actually - or it will be once I get the code completed) forum system (why? because I wanted to learn how to handle relational databases with PHP/MySQL, and then I just decided to complete the thing) ... it uses a multitude of 1-to-many relations across several tables, to keep track of categories, forums, threads, posts, perms, users and so on ... This particular chunk of code is part of the show forum code. What the first query does is pull out all the threads in the current forum ($forumID ... this valuable is set further up the code), and all the posts in each thread to find the last post in that thread. The second query is simply to figure out how many posts are in each thread. If at all possible, I'd like to combine these two query pulls (to keep the number of DB pulls per page down - there's currently 1 more, but going to be 2-3 more queries on this page than just this), but since I'm still rather n00bish at the SQL statements, I can't figure out how... Code in question: // get all the threads in current forum, and for each thread find the last post $threadquery = SELECT * FROM hf_threads,hf_posts WHERE hf_threads.forumID='$forumID' AND hf_posts.threadID=hf_threads.threadID GROUP BY hf_threads.threadID ORDER BY hf_posts.time DESC; $threads = mysql_query($threadquery) or die('Error in thread querybr'.mysql_error()); while($thread = mysql_fetch_array($threads)) { // info from hf_threads $threadID = $thread['threadID']; $threadtitle = $thread['threadtitle']; $threadmaker = $thread['createnick']; $threadstart = $thread['createtime']; // info from hf_posts $lasttime = $thread['time']; $lastposter = $thread['name']; // number of posts in thread $threadposts = mysql_fetch_array(mysql_query(SELECT COUNT(`postID`) AS numposts FROM hf_posts WHERE `threadID`='$threadID')) or die('Threadposts'); $numposts = $threadposts['numposts']; // output thread data echo(tda href=\showthread.php?forumID=$forumIDthreadID=$threadID\$threadtitle/a/td); echo(tdbBy:/b $threadmakerbrbAt:/b $threadstart/td); echo(td align=\center\a href=\editthread.php?forumID=$forumIDthreadID=$threadID\Edit/a/td); echo(td align=\center\$numposts/td); echo(tdbAt:/b $lasttimebrbBy:/b $lastposter/td\n); echo(td align=\center\input type=\Checkbox\ name=\del$threadID\/td); echo(/trtr\n); } Structure of tables in question: #TABLE hf_threads: threadID // ID of current thread forumID // ID of forum where the thread belongs userID // ID of thread creator (for perms reasons) createtime // time of thread creation createnick // name of thread creator threadtitle // title of thread sticky // not implemented yet closed // not implemented yet #TABLE hf_posts: postID // ID of post threadID // ID of thread where the post belongs userID // ID of poster (for perms reasons) name // name of poster time // time of post post // actual post content edit // tracking data of editings not that it's really relevant in this case, but these 2 tables are through the relations tied together with 8 other tables handling the forums and perms and users and statistics and such ... that's the reason why I want to keep the number of queries down, as it has to do join searches on a couple of these to handle the perms and other things on each page... TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] how to do query on partial field contents ???
hi gang I've mentioned this project before a couple of weeks ago ... just been busy with school, so haven't had time to work on it that much But anyways ... the test version is running here: http://metalbunny.net/computers/cpudb.php it's running with debug code, which should be obvious if you test it ... It's MySQL database ... testes it on both my webhost and my local test server, result is the same. If you fill in the search field and click the search button, I want it to list all records where the fields partially matches... but as it is now, it will only list records where the entire field matches. That is, if you search for Athlon, it will only list those where a field exactly is Athlon, whereas I want to list Athlon, Athlon XP, Athlon MP, and so on ... Figure that it would require some sort of wildcard, but the traditional ones I've tried didn't work, so I'm at a dead end once again... This is the 90 lines of code that generates the queries. Probably room for improvement, but then I'm no expert at this: $query = 'none'; $basequery = 'SELECT * FROM cpu'; if (isset($_GET['search'])) { $query = $basequery.' WHERE cpuID 0 '; $setorder = true; $query .= AND `model` LIKE '.$_GET['search'].'; // this block split for debug reasons $query .= OR `variant` LIKE '.$_GET['search'].'; $query .= OR `type` like '.$_GET['search'].'; } else if (isset($_GET['make']) || isset($_GET['model']) || isset($_GET['socket'])) { $query = $basequery; $setorder = true; if ($_GET['make'] != 'All' || $_GET['model'] != 'All' || $_GET['socket'] != 'All') { $query .= ' WHERE '; if ($_GET['make'] != 'All') { $query .= `make`='.str_replace('+',' ',$_GET['make']).'; } if ($_GET['make'] != 'All' $_GET['model'] != 'All') { $query .= ' AND '; } if ($_GET['model'] != 'All') { $query .= `model`='.str_replace('+',' ',$_GET['model']).'; } if ($_GET['model'] != 'All' $_GET['socket'] != 'All') { $query .= ' AND '; } if ($_GET['socket'] != 'All') { $query .= `socket`='.str_replace('+',' ',$_GET['socket']).'; } } $linkquery = str_replace(' ','+','make='.$_GET['make'].'model='.$_GET['model'].'socket='.$_GET['socket']); } if ($setorder) { switch ($_GET['order']) { case 'socket': $query .= ' ORDER BY socket'; break; case 'form': $query .= ' ORDER BY form'; break; case 'vcache': $query .= ' ORDER BY vcache'; break; case 'vcore': $query .= ' ORDER BY vcore'; break; case 'l2': $query .= ' ORDER BY l2'; break; case 'l1': $query .= ' ORDER BY l1'; break; case 'fsb': $query .= ' ORDER BY fsb'; break; case 'multi': $query .= ' ORDER BY multi'; break; case 'clock': $query .= ' ORDER BY clock'; break; case 'type': $query .= ' ORDER BY type'; break; case 'variant': $query .= ' ORDER BY variant'; break; case 'model': $query .= ' ORDER BY model'; break; case 'make': default: $query .= ' ORDER BY make'; break; } switch ($_GET['ad']) { case 'd': $query .= ' DESC'; break; case 'a': default: $query .= ' ASC'; break; } } The order part is of course not that relevant in this case, but I left it in the snip for the variables set in the query part to make sense... Question is simple: What do I change in the code to make it do partial field matches, instead of complete ??? Hoping for help :) ... TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Not having mysql_close() ... is that a big problem ???
hi gang simple question really (for once): I've written a forum system ... most of it (still lack the edit and perms things, but otherwise it's fully working), over the past 4-5 days ... but I've noticed that on some of the scripts I've forgotten the mysql_close () will that cause any protential problems, or should I go through the files and put it in where missing the thing is that it does a few includes criss-crossing a few files ... so finding where to put the damn thing is not always possible, with SQL pulls from 7 tables and such ... Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and mail forms
I wrote mine specially ... it runs with a preview phase which was pretty tricky to put in ... but the to-value is hardcoded in the script, cuz it just sends mail to [EMAIL PROTECTED] ... when the user fills in the form the script checks it for errors, and then either bounce back to the form if there are any errors, or to the preview if there's not (atleast I think I kept the preview in there, not sure anymore, been a long while) and then on to a thank you message I control it all using hidden form fields ... pretty simple ... it's coded to my old site-structure, before I started to use templates ... so it's a bit difficult to peel it out and show it to you though but will try if requested... FWIW Rene Fate would have it, that on Fri, 30 Jan 2004 09:41:11 -, Ricardo Lopes wrote: There are several ways to do this. Some people like the approach: if ($submit) { send_email(); } or you can put a hidden field in your form, like op: input name=op type=hidden id=op value=send / and use: if (isset($HTTP_GET_VARS['op']) ($HTTP_GET_VARS['op'] == 'send')) { send_email(); } and there are many others. by the way in the code you wrote you have: form method=post . and then you use: $id=$_GET['id']; --- if this is the same than $HTTP_GET_VARS['id'] this wont work Where does that mail function (is it a function?) go? The function sends an email, for more details consult the php manual. -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to do freeform text search across multiple fields ??
hi gang okay, freeform may not be the right term, but dunno what else to call it... I gave up finding anything with google, so now I'm turning to you lot This is an early beta version of the DB over all x86 CPUs ever made, that I'm working on: http://metalbunny.net/computers/cpudb.php It still has all the debug code, so should be pretty evident how it works the part in question is running off 1 table with 14 fields and hundreds of records (when all the data is entered into the DB atleast). the table is called cpu, fields are as follows: cpuID make model variant type clock multi fsb l1 l2 vcore vcache form socket What I wanna do is make it possible to search for partial content in model,variant, and type fields, all 3 at the same time, or each individually as the user wants...case insentive too ... But I have no idea what a query for that looks like ... none of my references do something like that Basically ... if you search for athlon, it should be able to find Athlon, Athlon XP, Athlon MP, and Athlon64 ... (which are all in the model field) ... or like you enter Thunderbird, and have model,variant, and type checked, it will be able to list only those that have Thunderbird in the type field... also ... how do I do range searches ... like listing all speeds from 500 to 1000 or something like that ??? yeah, I seriously need some decent documentation for constructing SQL search strings TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] CREATE TABLE problem
hmm ... I have unsigned auto_increment fields in all my tables that use unique field IDs... But i use very long counter fields as it's pretty big tables I have (or gonna be once I get them finished)... FWIW Rene Fate would have it, that on Thu, 22 Jan 2004 08:47:01 -, [EMAIL PROTECTED] wrote: I have had similar problems before. I have just created your table in MySQL. Seems you can't have an AUTO_INCREMENT column that is UNSIGNED. Doesn't mention this in the manual though. Neil Morgan Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Selecting last record only
Hi gang this is probably ridicously simple, but I'm new at this SQL stuff, so am a bit stumped. It's pretty simple: how do I get only the last record from a single table? Trying to convert my file-based website into DB-based, but can't quite figure out how to do this simple task. Table name is history, and only have 3 fields: histID, date, entry I need it to pick the last record without knowing the date of it. Got picking specific records down good, but be damned if I can figure this one out... TIA Rene Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Selecting last record only
thx a bunch :) ... figured it would be something simple ... :p Rene Fate would have it, that on Wed, 21 Jan 2004 11:55:23 -0800, Micah Stevens wrote: Reverse the order and limit the result to 1. For example, change: SELECT date, item from some_table To: Select date, item from some_table ORDER BY date DESC LIMIT 1 to get the latest dated item. -Micah Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] **Happy New Year**
A day early =) ... but happy new year to you too Rene Fate would have it, that on Tue, 30 Dec 2003 12:17:29 +0530, [EMAIL PROTECTED] wrote: Hi PHP Coders, Wishing you all a very happy and prosperous 2004. Aynsoft www.aynsoft.com Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL 4.x: Access denied for users when password is set...
Hi gang as subject says ... I've got a pretty weird situation. For all users I create in the 'user' table in 'mysql' DB, if I set a password, that user becomes unable to access any DBs ... it doesn't matter if I set the pw as ASCII or password, still won't allow access... I've tried making new users and copying all settings of the default root user (which I've not set a PW for cuz I loose access to the main DB when I do that) ... nomatter what, all users can access the DBs fine when no pw is set, but if a pw is set, MySQL reports access denied... and yes, it actually also does this when using the MySQL command line tools which makes it even weirder... This is a single-user test setup for development use only ... I run MySQL 4.0.14b on Windows XP Pro SP1 ... with Apache 2.0.48 and PHP 4.2.3 on CGI. Doesn't matter what kind of DB access I try; the MySQL binaries, PHPMyAdmin, custom made PHP/MySQL apps, or PHPBB ... if I set a PW for a user, that user becomes unable of accessing the DBs ... I've tried opening up the firewall for the MySQL server modules to access the Internet, but does not make a change ... hostname is set to 'localhost' in the mysql table (since all it has to work with are connections from the same windows session anyways)... is there any other place in the mysql table that I have to add/change stuff than 'user' to get this to work??? so lost here ... any help appreciated (and as far as I can tell, I've done every step in all MySQL install guides I could find ... atm I'm running on a restored version of the default mysql DB with the % users set ... since the firewall locks out remote access, I didn't think it's a big deal)... n00b at SQL TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL: How to properly extract fields from retrieved single record ???
Hi gang Still working on this DB for my gameclan not that it's essential what it's for... and be warned: Never worked with any form of DB in PHP before I began this project, so I'm a total n00b at these MySQL commands (and the manual for MySQL is by far nowhere near as good as the PHP one). Anyways, I'm retrieving a single record from the DB table, and then, because of the way the data needs to be displayed and manipulated, need to extract every single field from that record as a seperate value. I tried this: $num2edit = $_POST['edit']; $link = mysql_connect($host,$username,$password) or die('Could not connect : '.mysql_error()); mysql_select_db($database) or die('Could not select database'); $query = SELECT countryNum,country,nw,gov,strat,spy,troops,jets,turrets,tanks,ally,owner FROM a2a WHERE countryNum=$num2edit; $result = mysql_query($query); $countryNum = mysql_result($result,1,'countryNum'); $country = mysql_result($result,1,'country'); $nw = mysql_result($result,1,'nw'); $gov = mysql_result($result,1,'gov'); $strat = mysql_result($result,1,'strat'); $spy = mysql_result($result,1,'spy'); $troops = mysql_result($result,1,'troops'); $jets = mysql_result($result,1,'jets'); $turrets = mysql_result($result,1,'turrets'); $tanks = mysql_result($result,1,'tanks'); $ally = mysql_result($result,1,'ally'); $owner = mysql_result($result,1,'owner'); /* Free resultset */ mysql_free_result($result); /* Closing connection */ mysql_close($link); But all it gives me are errors like this: Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 350 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 351 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 352 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 353 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 354 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 355 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 356 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 357 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 358 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 359 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 360 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in E:\web\mpe\a2aedit.php on line 361 Obviously, the first mysql_result line is line 350 in the full code. I've also tried changing the row number to 0, but results in the same errors. I can't find any decent reference on the web that shows how to do this - field extraction - when there's only the single record. So I've been mixing code from various places. And I don't even know if it retrieves the right record, or any record at all (how do I test that?). Any help is highly appreciated :) TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to do error feedback when adding to indexed table?
Sorry if I was being vague. But naturally, about half an hour after I posted this, I found a simple and fool-proof way to do what I wanted: Simply just have MySQL lookup the ID after all the other error checking was done, like this: if (! isset($error)) { // DB check to ensure number is not already in DB // only do this if no other errors are present, because it consumes more // resources and time. $link = mysql_connect($host,$username,$password) or die('Could not connect : '.mysql_error()); // echo('Connected successfully'); mysql_select_db($database) or die('Could not select database'); $query = 'SELECT * FROM ally2ally WHERE id='.$_POST['num']; $result = mysql_query($query); $numrows = mysql_numrows($result); if ($numrows 0) { $error = 'The country number you entered already exists in the database. To edit an existing entry, please use the edit tool/function.'; } Well, this is a simple way to me atleast. This is run after initial prost procedure, and after all the other error checkers, so that it can bump it back to the edit form without going through the preview first. Thus I managed to figure it out on myself, although going about it quite differently from what I originally thought I had to. So, I'm so sorry for wasting everybody's time with this. Should perhaps be said that the edit tool mentioned in the code above isn't coded yet, so I might come back to you nice people once I get to that. But, to answer Sebastian's questions: At 19:15 24-08-2003, Sebastian Haag wrote: For a game clan I'm in (MPE 1A in Earth: 2025 - 1a), I'm working on a DB to store some rather game-specific info. It's only a single table in the DB, but the table is indexed by the field 'id'. Is that your Primary Key? Does this ID have a meaning to you, or is it random? Yes it is primary, and yes it has meaning. Each player in the game have a single country. Each country have a unique number, generated by the game. We use these numbers to indetify the countries, and thus it's only natural to use it for the same purpose in my database. Whenever someone wants to add data to the table, they fill out a form, hit submit and either get an error about wrong or missing data, or if ok get a preview before being able to finally add it to the table. As the script is now, it will attempt to add the data to the table, but if already there, nothing happens, and there's no response to the user about it. Do you mean if the ID is already there? Yes. The rest of the code is not essential in this aspect. I just need some help to figure out how to make the script tell the user that it can't add the data because the ID is already in the database...(that's the first field btw). I'm still not sure what exactly you want to do. But usually you make your ID an AUTO_INCREMENT. That way you don't have to worry about giving the same ID twice. I am aware that :-). Problem here is that the information stored in the database must match the content of the game, and therefor also use the same ID numbers. I should probably have mentioned that in my initial posting, sorry. The country we store information about, may only exist once in the database, therefor I use the country's number as ID. If I used an auto-incremented ID, it would be possible to enter the same information as many times as one would like, which is not desirable. If you don't know the game, I do realise that understanding what I talk about may be a bit difficult. I apologize for that. But if anyone have anymore questions to this project, I'll happily ask them. If it's about the game, better keep it offlist, thank you. Thanks for the help Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to do error feedback when adding to indexed table?
I'm not sure how to explain this, I'm totally new to MySQL and SQL as a whole, this is my first ever code using SQL at all...so plz bare with me here... I'm working with PHP 4.2.3 and MySQL 4.something (not certain what version the end-server runs). For a game clan I'm in (MPE 1A in Earth: 2025 - 1a), I'm working on a DB to store some rather game-specific info. It's only a single table in the DB, but the table is indexed by the field 'id'. Whenever someone wants to add data to the table, they fill out a form, hit submit and either get an error about wrong or missing data, or if ok get a preview before being able to finally add it to the table. As the script is now, it will attempt to add the data to the table, but if already there, nothing happens, and there's no response to the user about it. I want it to give a feedback message that the data is already in the table, and bump ppl back to the edit form with that message. For the other things the script checks, all it does is load the variable $error with the error-message and set a phase-flag to the right value, and then the page generation part knows that there's been an error, and regenerates the entry form with the submitted data. So what I want, is to have the add data part to just change the two variables needed for the error system to see there's something wrong, but I've got no idea what I need to change in the code to do that. I've searched google and the archives, but come up with nothing... Here's the add-data part of the code...the variables in the comment is just so that I know what kind of data are in each variable, rather than what they're actually set to in the DB: elseif ($phase == 3) { /* num / id = int country = string nw = int gov = string strat = string ally = string spies = int totdef = int totoff = int owner = string */ mysql_connect($host,$username,$password); @mysql_select_db($database) or die('Unable to select database'); $query = INSERT INTO ally2ally VALUES ('.$_POST['num'].','.$_POST['country'].','.$_POST['nw'].','.$_POST['gov'].','.$_POST['strat'].','.$_POST['spies'].','.$_POST['totdef'].','.$_POST['totoff'].','.$_POST['ally'].','.$_POST['owner'].'); mysql_query($query); mysql_close(); } the error message part is simply this: ?php // if there is an error in the submitted data, display the error message if (isset($error)) { ? table tr td?php echo($error); ?/td /tr /tablebr br ?php } ? The rest of the code is not essential in this aspect. I just need some help to figure out how to make the script tell the user that it can't add the data because the ID is already in the database...(that's the first field btw). TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Compiling 4.2.3 with MySQL ... what does it look for?
X-posted to PHP General, PHP DB, and MySQL Hi gang Attempting to get my Linux test-server working, but ran into a problem when making PHP... System is RedHat 8, Apache 1.3.27 (compiled myself, tested OK), MySQL 4.0.13. The Apache 2.0.40 and PHP 4.2.2 that came w. RH8 didn't work correctly, thus I've ventured into my own creation. Trying to build PHP 4.2.3 (because that's what my webhost runs, so need that version to test correctly) w. support for MySQL. Running ./configure --with-mysql=/[path to mysql] --with-apxs=/[path to apxs] Found the path to the apxs to be /usr/local/apache/bin/apxs, but for the life of me I cannot figure out what path to give it for MySQL. I installed MySQL from the the RPM files: MySQL-client-4.0.13-0.i386.rpm MySQL-devel-4.0.13-0.i386.rpm MySQL-embedded-4.0.13-0.i386.rpm MySQL-server-4.0.13-0.i386.rpm MySQL-shared-4.0.13-0.i386.rpm client and server first, the rest second ... used --force to get them in, because it complained about version issues with the one already there (even though the package manager was told not to put the Mysql in, it still put MySQL 3.something in...) When doing the configure above, I get this error: configure: error: Cannot find header files under /usr/include or whatever path I give it ... I'm having a hard time figuring out where the RPM puts everything, and an even harder time figuring out what path to stick to PHP ... Some detective work gave me these paths: MySQL (bins): /usr/bin /usr/share/mysql MySQL daemon (mysqld): /usr/libexec /usr/sbin MySQL headers (.h): /usr/include/mysql I've tried them all, but they all result in the above error. Anyone care to guess which path I should give to the configure? Or is it something else that causes this? I haven't ever used MySQL before, or any other SQL for that matter, so got 0 experience in getting the system up and running with it... TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Headers in body - what am I doing wrong???
At 22:48 11-06-2003, you wrote: You are already pretty far in determining the problem. Indeed the border between the header and content of a mail is an empty line. What if i just give a hint? Look closely: $from = \$name\ $address\r\n; $headers .= From: .$from.\r\n; See ? Yup ... too many header fields, and just one that ain't ... so hadn't noticed it ... PS some mailservers do not like \r\n, check the manual on mail() and see what they suggest, i think it was just \n. I actually wrote this according to the manual, ... this is an extract of the offline version of the PHP 4.2.3 I've got: Multiple extra headers are separated with a carriage return and newline. Note: You must use \r\n to seperate headers, although some Unix mail transfer agents may work with just a single newline (\n). Sounds like you're reversed there ;-) Mercury only cares about whether or not it's conformant to RFC822 and the siblings. Anything non-compliant is simply ignored or discarded ... but my webhost use sendmail on Linux, and since I've got another mailscript that uses the same basic format, and it works exactly as intended, then I don't think it's a problem with the \r\n header seperators... Thanks to y'all ... now I just gotta figure out why Mercury doesn't process the commands. ... commands sent by regular mail are processed ... so it must be a problem with the script... Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php