Re: [PHP-DB] MySQL Conditional Trigger
Original Message Subject: Re: [PHP-DB] MySQL Conditional Trigger From: Chris To: OKi98 Date: 8.12.2008 23:09 anything compared to NULL is always false Actually it's null. mysql> select false = null; +--+ | false = null | +--+ | NULL | +--+ 1 row in set (0.01 sec) mysql> select 1 = null; +--+ | 1 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) mysql> select 2 = null; +--+ | 2 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) unknown compared to anything is unknown. he was talking about plsql and condition evaluation (ends with true or false), your point is absolutely useless. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
Original Message Subject: [PHP-DB] MySQL Conditional Trigger From: Dee Ayy <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 31.10.2008 17:09 I don't think my trigger likes a comparison with a variable which is NULL. The docs seem to have a few interesting variations on playing with NULL and I was hoping someone would just throw me a fish so I don't have to try every permutation (possibly using CASE, IFNULL, etc.). If my ShipDate (which is a date datatype which can be NULL) changes to a non-null value, I want my IF statement to evaluate to TRUE. IF NULL changes to aDate : TRUE IF aDate changes to aDifferentDate : TRUE IF anyDate changes to NULL : FALSE In my trigger I have: ... IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN ... Which only works when ShipDate was not NULL to begin with. I suppose it evaluates the following to FALSE IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN (not liking the "NULL != '2008-10-31'" part) Please give me the correct syntax. TIA anything compared to NULL is always false NULL = NULL (NULL included) => false NULL != anything (NULL included) => false that's why IS NULL exists I would go this way: IF NVL(OLD.ShipDate, -1) != NVL(NEW.ShipDate, -1) THEN -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Apache crashes every time I call mysqli_fetch_assoc()
Original Message Subject: [PHP-DB] Apache crashes every time I call mysqli_fetch_assoc() From: Bonger O <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 9.7.2008 19:32 Hi, I have set up an environment on my Vista laptop comprising of Apache 2.2, MySQL Server 5.1 and Php 5.2.5. I'm using a simple php program that I found in a PHP/SQL book. The PHP program queries a MySQL table and traverses the results array of the query and should display on screen. However, there seems to be a problem with my environment and as every time I run the program I get the error "*Apache HTTP server stopped working and was closed*". Below is the code I am using, but I have managed to narrow the problem down to *$row = mysqli_fetch_assoc($result);* For some reason this is causing the crash. Would there be any reason that this function cant be found perhaps? I really have no clue!!! I had similar problem running Apache 2.2, MySQL 5.1 and PHP 5.2.6. - I had crash everytime I tried to connect to MySQL. When I executed the script with php.exe it worked okay so I think there is some error in php5apache2_2.dll. I played with various versions of PHP and Apache but the error remained, entries in event log were still the same: Application Failure Apache.exe 2.0.63.2 in php5ts.dll 5.2.6.6 at offset abda Application Failure httpd.exe 2.2.9.0 in php5ts.dll 5.2.7.7 at offset abda I havent found any solution (there are few bug reports on php.net) but they were closed because the bug was not "reproducable". I downgraded to MySQL 5.0 and everything works fine now. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Create Search engine with multiple form fields
Hello, yesterday I installed PHP 5.2.7 and whenever I use mysql_connect function Apache (2.2.9) crashes. Mysqli functions work without problem. Anybody knows why is this happening or has similar experience? OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL circular buffer
> (i.e. stack_id > 500 && stack_id < 601 vs where stack_id = 500 limit 100) stack_id between 501 and 600 (stack_id > 500 && stack_id < 601) is much better What I would like to know is if anybody has experience implementing this sort of data structure in MySQL (linked list?) or any advice. tables: process_table: IDProcess PK mail_table IDMail PK IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL Mail TEXT From VCH(255) TO VCH(255) DateModified DATE ON UPDATE CURRENT_TIMESTAMP Mailed TINYINT DEFAULT 0; -- code: define('MaxProccessTimeMinutes', 30); define('BatchCount', 100); INSERT INTO process_table VALUES (); $lnIdProcess = GetLastIDProcess(); label send_mail: $ldDateExpired = time() - MaxProccessTimeMinutes * 60; UPDATE mail_table SET IDProcess = $lnIdProcess WHERE Mailed = 0 AND ( IDProcess IS NULL OR ( IDProcess IS NOT NULL AND DateModified <= $ldDateExpired ); ) LIMIT BatchCount; while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE IDProcess = $lnIDProcess) { if (send_mail()) { UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail']; } } if there are other mails goto send_mail; else DELETE FROM process_table WHERE IDProcess = $lnIDProcess; this way more than one process can send mails, also if one process exits prematurely the other can send his emails later. Time to time run cron: DELETE FROM mail_table WHERE Mailed = 1; rebuild indexes on mail_table; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Manipulating Blob Fields
Original Message Subject: Re: [PHP-DB] Manipulating Blob Fields From: julian <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 14.2.2008 11:24 After some research I found a workaround, not a solution. Diggin in www.php.net I found the following (potential) bug apparently resolved time ago http://bugs.php.net/bug.php?id=35155 in relation to Bug #35155 prepared statement with blob field does not work Which is exactly what it is happening to me. It refers to the use a function mysqli_stmt_send_long_data. Apparently you only need to use this function if your data is larger than max_allowed_packet, which in my system is 16MB. The fact is, it is the only way I have managed to store pdfs files in the field. My pdf files a very small < 5Kb. Happy to have it working, but hesitant to understand why Regards. JCG I had similar problem. I needed to insert few thousands of rows. The code looked like this: $buffer = ''; $buffer_length = 0; $max_packet_size = 75; for ($i = 0; $i < sizeof($inserts); $i++) { $current_values = '(' . $inserts[$i]['col1'] . ', ' . $inserts[$i]['col2'] . ', ' . $inserts[$i]['col3'] . ')'; if (($buffer_length + strlen($current_values)) > $max_packet_size) { if (!mysql_query($buffer, $connection)) { //error handling } $buffer = ''; $buffer_length = 0; } $buffer .= ($buffer != '') ? ", $current_values" : "INSERT INTO table (col1, col2, col3) VALUES $values"; $buffer_length += strlen($current_values); } It was interesting that although MySQL's internal max_packet_size was more than 1 MB I was able to send only about 100kb long query. I have never figured out why does this happen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select...
Original Message Subject: [PHP-DB] Select... From: Miguel Guirao <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 15.1.2008 4:44 Hello List, I'm having kind of trouble to get done this: Select data from a table, except those data already in a second table. Actually, if there is a rowid in table2, I wont get it from table1, rowid is the key that relates both tables. I just can't express this with a SQL statement!! idequipomed is the key that relates both tables!! So, if idequipomed is already in Table2, I shouldn't get it from Table1. Any suggestions? Many ways to do this. Choose the solution that gives you the best performance. Solution 1: SELECT t2.idequipomed FROM table2 t2 WHERE NOT EXISTS ( SELECT 1 FROM table1 WHERE table1.idequipomed = t2.idequipomed ) Solution 2: SELECT idequipomed FROM table2 WHERE idequipomed NOT IN (SELECT idequipomed FROM table1) Solution 3: SELECT table2.idequipomed FROM table2 LEFT JOIN table1 ON table1.idequipomed = table2.idequipomed WHERE table1.idequipomed IS NULL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Looking for specific data
Original Message Subject: [PHP-DB] Looking for specific data From: DeadTOm <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 22.11.2007 3:22 There has to be an easier way to do this. I have a page with a list of 29 options utilizing checkboxes. The options that are checked get put into a mysql table as a 1 and the options not checked get entered as a 0. So the table looks like this idcs01 cs02 cs03 cs04 10 0 1 0 2 10 0 0 3 00 0 1 The problem I'm running into is getting these things back out of the table. I only want the columns where the answer is a 1. What I'm doing right now looks like this: $getPos = "SELECT * FROM pos WHERE id = '$id'"; $getPos_res = mysql_query($getPos,$conn) or die(mysql_error()); while ($getPos_info= mysql_fetch_array($getPos_res)) { $getPos_cs01 = $getPos_info['cs01']; $getPos_cs02 = $getPos_info['cs02']; $getPos_cs03 = $getPos_info['cs03']; $getPos_cs04 = $getPos_info['cs04']; } Then I use a series of IF statements to filter out the variables containing a 0 so that on another page I can view only the options they selected and leave out the ones they didn't. Obviously this takes a crazy amount of code to accomplish this way. Is there some way, using something like mysql_fetch_array or in my SELECT statement, to just get the data containing a 1? Or can someone propose an entirely different way of accomplishing the same thing? Thanks! depends if cs01..cs04 are related. *1. Related (option in multiple select)* something like "choose favorite colors: red/white/black/blue" there are 2 ways how to handle that: /a. m:n/ table customer ( idCustomer) table color_options ( idColor colorName) table customer_favorite_color ( idCustomer idColor) SELECT idCustomer FROM customer,customer_favorite_color WHERE customer_favorite_color.idCustomer=customer.idCustomer GROUP BY customer.idCustomer; / b. set /table customer ( idCustomer, favoriteColor <= varchar, you will store idColor separated by commas like for example '1,2,3' ) table color_options ( idColor colorName) SELECT idCustomer FROM customer WHERE favoriteColor IS NOT NULL; *2. Not related *$testColums=array("cs01","cs02","cs03","cs04"); $where=join("!=0 or ",$testColumns)."!=0"; $query="SELECT * FROM pos WHERE $where"; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php maximum characters in text field
VanBuskirk, Patricia napsal(a): It isn't sending to MySql, we use FileMaker Pro 9. A new record did not get made in the d'base... the confirmation email went out, but wherever info was supposed to kick back from the d'base (ie. Order number), it showed an "undefined variable" error. -Original Message- From: Instruct ICC [mailto:[EMAIL PROTECTED] Sent: Monday, October 15, 2007 1:05 PM To: php-db@lists.php.net Subject: RE: [PHP-DB] php maximum characters in text field I am starting to believe it is NOT the character length that is causing the problem. We have received other orders with more in it than that, and they came in ok. I think there may be something in the input that php does not like. I've been searching for code that will "clean up" input, possibly preg_replace? Was the order received, but the text was shorter than expected? Or was the order not "received" at all? Check your mysql query log. It may show failed queries where bad characters made the query fail, perhaps using ' or "? Use mysql_real_escape_string. _ Climb to the top of the charts! Play Star Shuffle: the word scramble challenge with star power. http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_oct post the code (form + script that proccesses passed variables + print_r($_POST) ), it'll make the things much quicker. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php maximum characters in text field
VanBuskirk, Patricia napsal(a): The info he was trying to submit was 883 characters, including spaces (counted in Word). Many times, people have very detailed descriptions they need to send in. Can you think of a way to work around this limitation? Also, what happened when he tried to submit is he got an email back with "Undefined variable" errors wherever the database was supposed to send back info, and no record was created. I guess you are using get method of http request which is limited by "maximum URL Length" (2083 chars in IE). Force the browser to use post by . OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] insert several data for one customer
Fahmi Basya napsal(a): Hi, All, I have problem to insert several data for one customer. description: the customer buy some product example: customer= jojo. he buy= hat,coffee and milk problem: i confused for inserting data to mysql, because i just can insert only one product to mysql, i have already use an array inside my php script for insert all variable product but when i push submit button the array only contain one product. question: 1. How is the way to create this script ...? i will very appreciate any suggestion, regards, Fahmi table 1: CREATE TABLE customer ( idCustomer INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (idCustomer) ) comment="table containing customer personal data" table 2: CREATE TABLE goods ( idGoods INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (idGoods) ) comment="table containing infos about goods" table 3: CREATE TABLE customer_goods ( idCustomerGoods INT NOT NULL AUTO_INCREMENT,#customer can buy the same stuff more than one time, it's up to you how you ensure uniqueness in this table idCustomer NOT NULL INT , idGoods NOT NULL INT , PRIMARY KEY (idCustomerGoods), FOREIGN KEY (idCustomer) REFERENCES customer (idCustomer) on delete cascade/restrict/set null, FOREIGN KEY (idGoods) REFERENCES goods (idGoods) on delete cascade/restrict/set null ) comment="goods bought by customer" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prevention for multiple submissions from the same form
TG napsal(a): But what if the data being inserted isn't unique? What if it's a voting form that says "Do you like pie? Yes/No"? True, it could record the response and the userid or IP address so that would be a unique pairing but what if the vote was anonymous and you allowed multiple people from the same IP address (ie behind a firewall/proxy) to submit votes? simple solution with timestamp - you allow to vote from 1 ip once in a given time (day, hour, minute, second, depends on you) There could be cases where the data isn't necessarily unique Sorry, I dont know any. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] basic form entry
most likely you have a problem in your query but you dont understand how to use mysql_error. from http://www.php.net/manual/en/function.mysql-error.php : string *mysql_error* ( [resource $link_identifier] ) Returns the error text from the last MySQL function echo mysql_error(mysql_connect($host,$username,$psswd)); you see now? you are creating new connection. correct code should appear similar to this: $connection=mysql_connect($host,$username,$psswd) or die ("errror in access db"); mysql_select_db($db,$connection); if (!mysql_query ("INSERT INTO users (First,Last,Phone,Mobile,Email,Web) VALUES ('$first_name','$last','$phone,'$mobile','$email','$web') ",$connection)) echo("error ".mysql_error($connection)); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prevention for multiple submissions from the same form
T K napsal(a): I've learned that this is possible to make and send a unique id in hidden input, and check if the id is used before database query is issued. (PHP Cookbook [O'reilly]). Why? You should always ensure that only unique data are inserted into database. This is not just about multiple submissions. Visitor can submit form then forgets about that so he goes back and submits the same thing again. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Data not fetching in the textfield.
Niel Archer napsal(a): Your variable value is not being inserted because you have used single quotes for the echo, not double quotes as the nabble example does. Special characters and variables are only interpreted in double quotes, they are treated literally in single quotes. -- Niel Archer Yes, also "disabled" field is not even sent -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Pages not fully loading
ioannes napsal(a): There is a lot of undefined index and undefined variables when I turn on error reporting. lol? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Maximum execution time setting - How to
Chris Carter napsal(a): Hi, How to fix this in cPanel Fatal error: Maximum execution time of 30 seconds exceeded in "whatever the URL" Or may be its not the right forum for this query. Thanks Chris http://www.php.net/manual/cs/function.set-time-limit.php http://www.php.net/manual/cs/ref.info.php#ini.max-execution-time -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Optimizing Query
Arie Nugraha napsal(a): Hi list, I Have a problem with my query that fetch about 22.000 records from database, the query is like this : SELECT SQL_CALC_FOUND_ROWS DISTINCT b.biblio_id, b.title, a.author_name FROMbiblio AS b LEFT JOIN biblio_author AS ba ON b.biblio_id=ba.biblio_id LEFT JOIN mst_author AS a ON ba.author_id=a.author_id WHERE b.biblio_id IS NOT NULL GROUP BY b.biblio_id ORDER BY b.input_date DESC LIMIT 0, 10 it took about 6 seconds to complete the query in localhost. I already make an Indexes on each table, but still the performance is slow. Anyone have an idea how to optimize the query so result is faster? Thx optimalisation requests should be supplied with result of explain and table structure. Without that it is just guessing what may went wrong. OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Slooooow query in MySQL.
Rob Adams napsal(a): I have a query that I run using mysql that returns about 60,000 plus rows. It's been so large that I've just been testing it with a limit 0, 1 (ten thousand) on the query. That used to take about 10 minutes to run, including processing time in PHP which spits out xml from the query. I decided to chunk the query down into 1,000 row increments, and tried that. The script processed 10,000 rows in 23 seconds! I was amazed! But unfortunately it takes quite a bit longer than 6*23 to process the 60,000 rows that way (1,000 at a time). It takes almost 8 minutes. I can't figure out why it takes so long, or how to make it faster. The data for 60,000 rows is about 120mb, so I would prefer not to use a temporary table. Any other suggestions? This is probably more a db issue than a php issue, but I thought I'd try here first. 60k rows is not that much, I have tables with 500k rows and queries are running smoothly. Anyway we cannot help you if you do not post: 1. "show create table" 2. result of "explain query" 3. the query itself OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP/MySQL UTF-8 SNAFU
Charles Sheinin napsal(a): Ok, so I have a fully UTF-8 MySQL database with a fully UTF-8 table. I have a php page which has "content="application/xhtml+xml; charset=utf-8" />" at the top of the html section and "mysql_set_charset("utf8");" after my connection (it's php 2.2.3, so that's more or less the same as "mysql_query("SET NAMES 'utf8'");/("SET SESSION character_set_client = "utf8");, though I tried all that too, anyway). I have all my php.ini mbstring stuff configured for utf-8: mbstring.language = Neutral mbstring.internal_encoding = UTF-8 mbstring.http_input = auto mbstring.encoding_translation = On mbstring.detect_order = auto and for what it's worth, echo mb_internal_encoding(); reports "UTF-8". when I run a query that pulls out (in this case, Japanese, but say any) UTF8 data, it displays properly in MySQL Query Browser. With php in the above context however, it does not. Instead, it prints a ? corresponding to each character. When I use mb_detect_encoding(); on each such string, it tells me they are encoded as ASCII. The nearest I can tell is that somewhere between using mysql_query() and mysql_fetch_row(), things got messed up. Is this a bug? Is there something I'm missing? Any clues? Help! do you have ** header in your script? OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] variable with NULL value
Hi, one more question. Why the variable, that contains NULL value appears to be not set. When you try to echo it the php does not produce warning about using undefined variable. //$connection contains database connection handler $result=mysql_query("select NULL as value",$connection); $tmp=mysql_fetch_array($result); $foo=$tmp["value"]; if (!isset($foo)) echo("\$foo is not set but does not produce warning -$foo-"); OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] weird comparsion
Ford, Mike wrote: On 03 May 2007 12:30, OKi98 wrote: I know about identity operator (===) but with == operator 0 is false and "foo" is true No, that's not correct. , try this: $foo=0; $bar="bar"; if ($foo) echo("$foo is true, "); else echo("$foo is false, "); if ($bar) echo("$bar is true, "); else echo("$bar is false, "); if ($foo==$bar) echo("$foo==$bar"); returns "0 is false, bar is true, 0==$bar" That's because you've got loads of implicit type conversions going on there, so you're not comparing like with like. For if ($foo) ... and if ($bar) ...: within the context of the if(), both $foo and $bar are implicitly converted to Boolean: - (bool)0 is FALSE - (bool)"any non-empty() string" is TRUE On the other hand, for if ($foo==$bar) ...: in the context of the == comparison, $bar is converted to a number, and any string not beginning with a numeric character converts to numeric zero -- so you get a comparison of zero with zero, which is, of course, TRUE! oh I didnt know that, I thought the number will be converted into a string. Thanks alot. I have one more question but I ask in separate thread :) Oki98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] weird comparsion
Mike van Hoof wrote: http://nl3.php.net/manual/en/language.operators.comparison.php try using === (3x =) for comparison - Mike OKi98 schreef: Hello, Why does anything compared to 0 return true? I know it might seem to be a bit off-topic, but for me it is important for detecting if NULL value in table has been changed (for example NULL is changed to 0). if ("foo"==0) echo("foo equals to 0"); OKi98 __ Informace od NOD32 2235 (20070502) __ Tato zprava byla proverena antivirovym systemem NOD32. http://www.nod32.cz I know about identity operator (===) but with == operator 0 is false and "foo" is true, try this: $foo=0; $bar="bar"; if ($foo) echo("$foo is true, "); else echo("$foo is false, "); if ($bar) echo("$bar is true, "); else echo("$bar is false, "); if ($foo==$bar) echo("$foo==$bar"); returns "0 is false, bar is true, 0==$bar" OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] weird comparsion
Hello, Why does anything compared to 0 return true? I know it might seem to be a bit off-topic, but for me it is important for detecting if NULL value in table has been changed (for example NULL is changed to 0). if ("foo"==0) echo("foo equals to 0"); OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Foreign Key Issue
Luchino - Samel wrote: *Question No. 01* there are some kind of foreign key in database Some of them want give any error when you delete from the table where the foreign key is in, this cause the remove is "recursive" and it delete also the ORDER linked to the CUSTOMER (CASCADE foreign key). In other kind of foreign key (I don't remember the name) you cannot delete CUSTOMER if there is some ORDER linked to them. restrict hope this will help c-ya 2007/3/26, Lasitha Alawatta <[EMAIL PROTECTED]>: Hello, I have 2 issue, regarding MySQL "Foreign Key". I have two tables; Table 01 *CUSTOMER* column name characteristic SID Primary Key Full_Name Table *ORDERS* column name characteristic Order_ID Primary Key Order_Date Customer_SID Foreign Key Amount When I run "ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);" that sql statement, I inserted 2 records to both tables. * * *Question No. 01.* Then I removed 1 record from CUSTOMER table. But It want give any error message. It should give an error message, because in ORDERS table already have some records relevant to the deleted customer record in CUSTOMER table. you have restrict constraint on Customer_SID in table orders. You have 2 options: 1. delete from orders where Customer_SID=foo delete from customer where SID=foo 2. read http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PC Inventory System
christine wrote: Hi all, I need some help to decide the best db design for PC Inventory System. There are 2 solution that I thought, you can see it at the attachment. Which is the best solution ? Is there another solution ? Regards & Thanks, Christine Solution 3: Table pc pc_id Table component component_id component_type (enum) stuff_code desc Table pc_component pc_id (fk) component_id (fk) OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql_real_escape_string
Ron Piggott wrote: I am creating a form where I am using $web_site_# for the various fields. At the present time there are 11 fields I am asking the user to key in. I am wondering if there is a slick way to use the mysql_real_escape_string command with this so $web_site_1 = mysql_real_escape_string($web_site_1); $web_site_2 = mysql_real_escape_string($web_site_2); $web_site_3 = mysql_real_escape_string($web_site_3); ... $web_site_11 = mysql_real_escape_string($web_site_11); would be replaced by some type of loop for ($i=1;$i<=11;$i++) ${"web_site_$i"}=mysql_real_escape_string(${"web_site_$i"}}; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
php-db@lists.php.net
Peter Beckman wrote: Because I'm trying to point out a problem with PHP, where setting a reference when the other side is undefined or not set, PHP creates a reference to a previously non-existent array item, just by setting a reference. I don't think that should happen. And? what's wrong with that. The reference can be used in the future - I think thats why it doesnt produce any error message. ie. $array=array("1"=>"a","3"=>"c"); $ref=&$array["2"]; $array["2"]="b"; echo($ref); OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Trying to add primary key to existing database.
I think you also should read this http://en.wikipedia.org/wiki/Database_normalization -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] setting SQL variable
marga wrote: Is not possible to use mysql_insert_id() and assing it in a php variable, because mysql_insert_id() is executed after the COMMIT, I think. wrong, it is possible to use mysql_insert_id() during the transaction. the code should look like this: mysql_query("start transaction"); if (mysql_query(insert into first table)) { $id=mysql_insert_id(); if (!mysql_query(subsequent inserts)) $error=1; } else $error=1; mysql_query((isset($error)) ? "rollback" : "commit"); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Count's in one Select.
Ed wrote: SELECT DISTINCT u.*, t.*, (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id ) AS 'COUNT', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND t.jobtype = 'Navision') AS 'Navision', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND t.jobtype = 'Abuse'') AS 'Abuse', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND t.jobtype = 'Tickets') AS 'Tickets', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id) AS 'MegaTotals' FROM users u, taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id, t.user_id ORDER BY COUNT DESC I quess you are wrongly using aliases - try this and let me know: SELECT DISTINCT u.*, t.*, (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id) AS 'COUNT', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = 'Navision') AS 'Navision', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = 'Abuse'') AS 'Abuse', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = 'Tickets') AS 'Tickets', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id) AS 'MegaTotals' FROM users u, taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id, t.user_id ORDER BY COUNT DESC OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Paginatating PHP
Chris Carter wrote: >There is enough data to display this result and to paginate but its simply showing the >else condition which does not have NEXT or PREVIOUS hyperlinked. >... >$query_count = "SELECT count(*) FROM students"; >$result_count = mysql_query($query_count); >$totalrows = mysql_num_rows($result_count); well, the result is always 1 (even if no students are present), you need to use some mysql_fetch_* function to get the count of students ie. $tmp=mysql_fetch_row($result_count); $totalrows=$tmp[0]; OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Diff between 2 date
I use CalcDateDiff.php class and it works pretty good! Why use classes for such a simple calculation? It's the matter of 2 rows of code $dif_sec=mktime(0,0,0,10,6,2006)-mktime(0,0,0,10,5,2006); echo("diff is ".($dif_sec/(24*60*60))." days"); OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to stop the server timing out
Hi, > Is there anything I can do, to get the page to run completely sure, contact the hosting company to prolong the maximum execution time on that script > or speed up how the page works maybe, but I dont see the source code > The page does a select from a table and inserts the data into another table do you use insert ... select syntax? OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] user directory from a form
Hi, this is not a php but javascript. Next time write to a proper forum. You need to transfer the full path into other variable. onchange="document.getElementById('id_full_path').value=this.value;"> OKi98