jworisek

The only thing you can really do is put a visual watermark in the image.

Just curious about index types (haven't found quite what I was looking for in the manual, but maybe Im just searching the wrong key words). What exactly are the differences between Primary, Unique, and Index? As I understand it: Primary: only unique entries for specified columns are allowed and null values aren't allowed. Unique: only unique entries for specified columns are allowed and null values are allowed. Index: doesn't require uniqueness? but in joining tables all work in the same sense correct?

without more info... something like this maybe? SELECT sum(appointments) as num_appointments, user,.... FROM .... WHERE date='xxxx-xx-xx' and.... GROUP BY user ORDER BY num_appointments ASC LIMIT 1;

So you are basically saying run the same query in the subquery? I have other instances of a very similiar query where it doesn't get just an order_id value... It might get a customer_id or something else. But basically I can join it together the same basic way for more complex queries?

oh, sorry... I have the code on a PC not connected to the web so I typed the query and had a typo there. When I try to place the line_info_id=N.line_info_id into the sub query I get: Unknown table 'N' in where caluse if I do it without a where caluse for the subquery it works fine but pulls 8,601 rows from SD2 and 21,578 rows from shipping_data (every row in the table).

I have something similiar I think... I have a search page to allow users to search orders using any combination of information and order the data in different ways. What I do is take the data that they want to use in the search and sort it into different arrays. $table_array[] contains all the tables that are used to join together to get the information for that one item. $search_array[] describes how items are linked together $order_array[] describes how to order them. I then use array_unique on each and implode them into strings and put them into a sql statement: SELECT * from $table_string where $search string order by $order_string EX: [code] // Customers $search_array[]="O.customer_id=C.customer_id"; $search_array[]="C.customer_id='$var_array[requiredcustomer]'";      $table_array[]=('orders O'); $table_array[]=('customers C'); $order_array[]=('customer_name ASC'); // Shapes $search_array[]="O.order_id=LI.order_id"; $search_array[]="LI.size_id=X.size_id"; $search_array[]="X.shape_id=S.shape_id"; $search_array[]="S.shape='$var_array[shape]'"; $table_array[]=('orders O'); $table_array[]=('line_info LI'); $table_array[]=('sizes X'); $table_array[]=('shapes S'); //Date sort $order_array[]=('O.date_in ASC'); $searches=array_unique($search_array); if(!$searches){         $search_sql=""; } else{         $search_sql=implode(" AND ",$searches);         $search_sql="WHERE $search_sql "; } $tables=array_unique($table_array); if(count($tables)==0){         $tables_sql=""; } else{         $tables_sql=implode(",",$tables); } $order=array_unique($order_array); if(count($tables)==0){         $order_sql=""; } else{         $order_sql=implode(",",$order); } gives me: $sql="SELECT * FROM Orders O, Customers C, line_info LI, sizes X, shapes S WHERE O.customer_id=C.customer_id AND "C.customer_id='$var_array[requiredcustomer]' AND O.order_id=LI.order_id AND LI.size_id=X.size_id AND X.shape_id=S.shape_id AND S.shape='$var_array[shape]' ORDER BY customer_name ASC, O.date_in ASC"; [/code] Thats just a small portion of the option I use, I was too lazy to stick them all on here ;)

Heres my query: [code] SELECT sum(received) items_received, order_id, sum(received)-IF(ISNULL(sum(shipped)),0,sum(shipped)) as items_shipped from line_info N INNER JOIN incoming_data ID ON (ID.line_info_id=N.line_info_id) LEFT JOIN (SELECT sum(pieces_shipped) as shipped, line_info_id from shipped_data group by line_info_id) as SD2 ON (SD2.line_info_id=N.line_info_id) where order_id='$order_id' group by order_id [/code] Right now it sums the entire shipping_data table with the subquery before using just the one that links the tables. Is there a way to pass to the subquery where the main query is at (with respect to the N.line_info_id)? Or is there a better way to structure this query so that it doesn't do unnecessary work? There will always be a record in the incoming_data table, but there may be no rows or multiple rows (due to multiple shipments) for each item in the shipping_data table. Thanks

sorry, moved the post to the mysql forum. my bad. :/

shouldn't it look like below with the trailing period ? [code] $query = 'SELECT * FROM codes WHERE code = '.addslashes($_GET['code']).''; [/code]

like I said, place the echo [b]inside the loop[/b] so we can see whats going on instead of just showing me the last query that we know works. the problem is that you are not submitting your query inside the loop... everytime the loop runs your data is overwritten so of course only the last row is run. Try this: [code] for ($i=0;$i<count($_POST[event]);$i++){   $usql = "UPDATE weather SET   event='$event[$i]',   hrs='$hrs[$i]',   closed='$closed[$i]',   notes='$notes[$i]'   WHERE row_number='$row_number[$i]'"; // compiles query   $dosql = mysql_query($usql); // executes query } [/code]

the problem is that "References" is a reserved word in MySQL... you should never be using it as a table name or any name if possible... it will save you a lot of troubles later. But yes, you can use the backticks in order to use reserved words as names if you really want to. [a href=\"http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html[/a]

Save yourself a huge headache and just echo the values instead of trying to do queries when you are testing code. Post and echo of all 5 values from inside the loop to see if it is actually giving different values. ... and a quick tip, never use <font> tags... you can just stick that in your div tag like so: [code] <div align="center" style="color:#000000;">           <input name="notes[]" type="text" id="notes" size="50" maxlength="200"  value="<?php echo"$row[notes]" ?>" /> </div></td> [/code]

Well you only made changes for event and nothing else... I just used event as an example since Im too lazy to type the whole thing out ;) try either of these to get it working... sometimes it has issues when calling a multidimensional array.. so then just say [code] $event=$_POST[event]; $hrs=$_POST[hrs]; $closed=$_POST[closed]; $notes=$_POST[closed]; $row_number=$_POST[row_number]; for ($i=0;$i<count($_POST[event]);$i++){ $usql = "UPDATE weather SET event='$event[$i]', hrs='$hrs[$i]', closed='$closed[$i]', notes='$notes[$i]' WHERE row_number='$row_number[$i]'"; // compiles query } [/code] or [code] for ($i=0;$i<count($_POST[event]);$i++){ $usql = "UPDATE weather SET event='".$_POST[event][$i]."', hrs='".$_POST[hrs][$i]."', closed='".$_POST[closed][$i]."', notes='".$_POST[notes][$i]."' WHERE row_number='".$_POST[row_number][$i]."'"; // compiles query } [/code]

change your inputs to: (adding [] after each input name) [code] <input name="event[]" type="text" id="event" size="50" maxlength="200"  value="<?php echo"$row[event]" ?>" /> [/code] then when you are doing the update, you can just do [code] for ($i=0;$i<count($_POST[event]);$i++){   $sql=update weather set event='$_POST[event][$i]',....etc where WHERE row_number='$_POST[row_number] [$i]' } [/code]

you can only have one value per variable name... each time that cycles through, it overwrites the previous loops data. So in the end only the last row is there. On your page that submits the query to update the database, comment out the update query and echo your post variables and you will see this.

are you trying to update many records in the table that all have the same primary key? You can't have duplicates of primary key values, so your update query will only ever update 1 row if you are using where [primary_key]='value'.

[!--quoteo(post=350281:date=Feb 28 2006, 11:04 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Feb 28 2006, 11:04 AM) [snapback]350281[/snapback][/div][div class=\'quotemain\'][!--quotec--] Just keep in mind that I don't think I know enough about the tables to consider problems .You'll have to make sure that if there are any errors that you'll find them. [/quote] Believe me, there will be more hours of my time put to testing this than I care to think about at the moment ;)

Thanks a lot for all the help! After thinking about it a bit I will probably be pulling the vaules for all the tables as well because it would be pretty useful for debugging if I can post a small note on the report with the table used highlighted and all the other tables listed with prices they have. At least until we get all the bugs worked out.

after screwing around with it for a bit I did get it to work using the IFNULL suggestion, although I had to change it to say [code] SELECT IFNULL(BH.price,IFNULL(BM.price, BL.price)) AS price [/code] Or else it wouldn't return any price results. is there a way to return some string variable as well to let me know which table was selected to be used? That would be really helpful for testing and debugging. If not I can always just select BH.id as BHid, BM.id as BMid, etc and then just use php to check for precedence and print out a string based on that.

by precedence I simply mean that it should check bill_low then bill_medium, then bill_high. If there are any rows in bill_high, it should be taken over the other two, then bill_medium, etc. Each of the tables has a price column so what I wrote works fine if there is a value in the bill_high, but if there is no record in bill_high it returns one row but no price. Is there a btter way to word a query like that to obtain the results or should I just stick with 3 seperate queries and comparing them afterword?

Well I had to change a few things up, but I did get it working. It did bring up another question (since Im looking at optimizing reports that used to use multiple queries), when I want to apply billing information for line item, there are 5 different structures of billing set up in an order of precedence (ex standard billing is the lowest and specific order billing is the highest). Can I use a query like below to pull out the highest precedent price for billing? [code] $sql="SELECT price FROM bill_type BT LEFT JOIN billing_low BL ON (BL.bill_type_id=BT.bill_type_id) LEFT JOIN bill_medium BM ON (BM.bill_type_id=BT.bill_type_id) LEFT JOIN bill_high BH ON (BH.bill_type_id=BT.bill_type_id) where ..... " [/code]

I did try another range and it seemed to stay pretty much the same... If this becomes an issue I will most likely just prune the table since we don't really need to access the information after the order has been shipped.

I made that update and it is still checking all the rows in the table...

[code] | committed_new | CREATE TABLE `committed_new` (   `committed_id` int(11) NOT NULL auto_increment,   `order_id` int(11) NOT NULL default '0',   `line_info_id` int(11) NOT NULL default '0',   `initial_commit_date` date NOT NULL default '0000-00-00',   `current_commit_date` date NOT NULL default '0000-00-00',   `comments` varchar(200) NOT NULL default '',   `history` text NOT NULL,   PRIMARY KEY  (`committed_id`),   UNIQUE KEY `order_id` (`order_id`,`line_info_id`),   KEY `line_info_id` (`line_info_id`),   KEY `current_commit_date` (`current_commit_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | [/code] [code] +---------------+------------+---------------------+--------------+---------------------+-----------+-------------+----- -----+--------+------+------------+---------+ | Table         | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_ part | Packed | Null | Index_type | Comment | +---------------+------------+---------------------+--------------+---------------------+-----------+-------------+----- -----+--------+------+------------+---------+ | committed_new |          0 | PRIMARY             |            1 | committed_id        | A         |         704 | NULL | NULL   |      | BTREE      |         | | committed_new |          0 | order_id            |            1 | order_id            | A         |        NULL | NULL | NULL   |      | BTREE      |         | | committed_new |          0 | order_id            |            2 | line_info_id        | A         |         704 | NULL | NULL   |      | BTREE      |         | | committed_new |          1 | line_info_id        |            1 | line_info_id        | A         |          10 | NULL | NULL   |      | BTREE      |         | | committed_new |          1 | current_commit_date |            1 | current_commit_date | A         |          39 | NULL | NULL   |      | BTREE      |         | +---------------+------------+---------------------+--------------+---------------------+-----------+-------------+----- -----+--------+------+------------+---------+ [/code]

I updated to try that latest query and at least with preliminary checks it seems to be working correctly... It still is trying to call the entire committed_new table, only now it does it once for each subselect. heres the explain select on it: [code]id: 1 select_type: PRIMARY table: type: ALL possible_keys: key: key_len: ref: rows: 695 Extra: id: 1 select_type: PRIMARY table: type: ALL possible_keys: key: key_len: ref: rows: 382 Extra: id: 3 select_type: DERIVED table: CN type: ALL possible_keys: key: key_len: ref: rows: 704 Extra: Using temporary; Using filesort id: 3 select_type: DERIVED table: S type: ref possible_keys: PRIMARY,order_id key: order_id key_len: 4 ref: CN.order_id rows: 2 Extra: id: 3 select_type: DERIVED table: SD type: ref possible_keys: shipping_id key: shipping_id key_len: 4 ref: S.shipping_id rows: 3 Extra: id: 2 select_type: DERIVED table: CN type: ALL possible_keys: current_commit_date key: key_len: ref: rows: 704 Extra: Using where; Using temporary; Using filesort id: 2 select_type: DERIVED table: LI type: ref possible_keys: PRIMARY,line_info key: PRIMARY key_len: 4 ref: CN.order_id rows: 3 Extra: id: 2 select_type: DERIVED table: ID type: ref possible_keys: line_info_id key: line_info_id key_len: 4 ref: LI.line_info_id rows: 1 Extra: [/code]