Jump to content

wickning1

Members
  • Posts

    405
  • Joined

  • Last visited

    Never

Everything posted by wickning1

  1. This is the kind of problem that is difficult to troubleshoot from a distance. Usually it happens when you have badly written queries on your site, but it can also happen if PHP threads crash or are hanging open. MySQL's maximum number of connections is 100, so if you have a lot of users and a few slow queries it's not unusual to run into this problem. You can alleviate the problem by increasing the maximum number of connections in your MySQL configuration file, identifying your slowest queries and fixing them, or decreasing the default timeout for MySQL connections in your php.ini file. You should also make sure that your PHP application opens one and only one database connection per page load.
  2. Floating point arithmetic is always screwy on computers because converting a decimal to binary cleanly isn't possible. Just make sure you always round things to the precision you need. In MySQL ROUND(2.599999, 2) = 2.60
  3. Store it in MySQL exactly as you get it from the user. Then when you are displaying it, use something like: [code]echo nl2br(htmlspecialchars($row['text']));[/code] That will replace line breaks with <br> and will also replace < with &lt; and > with &gt; so that their HTML shows up as HTML text instead of being rendered.
  4. [code]preg_match('/^[0-9]{2}\/[0-9]{2}\/[0-9]{2}$/', '01/01/2005' );[/code] ^ means "beginning of string" $ means "end of string" So if you want to match the whole string, use them both.
  5. Old version of MySQL. My query requires MySQL 4.1+. Your best option in MySQL 4.0 or less is to use a temporary table and do it in 3 queries: [code]CREATE TEMPORARY TABLE user_averages (ID INT NOT NULL DEFAULT 0, aver FLOAT NOT NULL DEFAULT 0.0) TYPE=MyISAM INSERT INTO user_averages (ID, aver)     SELECT ID, AVG((score-rating)*113/Slope) FROM scores GROUP BY ID UPDATE users u INNER JOIN user_averages a ON a.ID=u.ID SET u.handicp=ROUND(a.aver*.96,1)[/code]
  6. ALTER TABLE will change table and column names, ALTER DATABASE will change database name... See the documentation at www.mysql.com for more details.
  7. I'll try and illustrate what's happening. <!-- BEGIN example_block --> {example_block.S_LOOPTEST}<br /> <!-- END example_block --> you run assign_block and it does this: <!-- BEGIN example_block --> {example_block.S_LOOPTEST}<br /> <!-- END example_block --> Value: 0<br /> Run it again and you get: <!-- BEGIN example_block --> {example_block.S_LOOPTEST}<br /> <!-- END example_block --> Value: 1<br /> Value: 0<br /> Run it again and you get: <!-- BEGIN example_block --> {example_block.S_LOOPTEST}<br /> <!-- END example_block --> Value: 2<br /> Value: 1<br /> Value: 0<br /> Run delete_block and you get: Value: 2<br /> Value: 1<br /> Value: 0<br /> Now I don't know exactly what you're shooting for but you could fix it by printing the values above the block: [code]   function assign_block ( $block_name, $array )       ...       # Replace keys with old keys + block in new_tpl.       $this->new_tpl = str_replace ( $match[ 0 ], $block . $match[ 0 ], $this->new_tpl );    }[/code]
  8. Yeah that's pretty close. It looks like there would be naming ambiguities with that, but if it's working, I guess it's ok.
  9. Nothing technically wrong there. Let's see some more code.
  10. This will set the handicap (rounded to one decimal place) in one query: [code]UPDATE users u INNER JOIN (     SELECT ID, AVG((score-rating)*113/Slope) as aver FROM scores GROUP BY ID ) d ON d.ID=u.ID SET u.handicp=ROUND(d.aver*.96, 1)[/code]
  11. You need to use a subquery that finds the objectid with MAX(date) with a GROUP BY. I don't have the energy to write it up but I will try to later.
  12. I modified my earlier reply.
  13. Well, I really just fixed the errors in your query, but I can't figure out exactly what data you are wanting to be returned. Maybe you should explain in more detail. From your code it looks like all you need is a single table query: [code]SELECT Invoice_Number AS InvoiceID, Subtotal AS sub FROM Orders WHERE Customer_ID='$custid' AND month='$month' AND year='$year'[/code] Why doesn't that work?
  14. These new kontera advertisement links are terrible. My whole browser hangs while the javascript runs, and runs, and runs. I've been hanging around for a couple months doling out advice, but this is making me want to stop.
  15. You don't need DISTINCT or GROUP BY. You're just missing a join condition for Invoice_Record. It doesn't have any link into the rest of the tables. If you use the INNER JOIN ... ON syntax, this becomes painfully obvious: [code]SELECT o.Invoice_Number AS InvoiceID, o.Subtotal AS sub, c.catname AS CatName FROM Invoice_Record ir INNER JOIN Orders o ON ??????????? INNER JOIN Customers cu ON cu.customerid=o.Customer_ID INNER JOIN Order_Details d ON d.Invoice_Number=o.Invoice_Number INNER JOIN Products p ON p.Product_ID=d.Product_ID INNER JOIN categories c ON c.catid=p.Product_Cat_ID WHERE o.Customer_ID='$custid' AND o.month='$month' AND ir.month='$month' AND o.year='$year'[/code] Here's my final cleaned up version of your query, guessing that Invoice_Record has an Invoice_Number column: [code]SELECT o.Invoice_Number AS InvoiceID, o.Subtotal AS sub, c.catname AS CatName FROM Invoice_Record ir INNER JOIN Orders o ON o.Invoice_Number=ir.Invoice_Number INNER JOIN Customers cu ON cu.customerid=o.Customer_ID INNER JOIN Order_Details d ON d.Invoice_Number=o.Invoice_Number INNER JOIN Products p ON p.Product_ID=d.Product_ID INNER JOIN categories c ON c.catid=p.Product_Cat_ID WHERE o.Customer_ID='$custid' AND o.month='$month' AND o.year='$year'[/code]
  16. [code]SELECT CAST(`price`AS DECIMAL) AS myprice FROM table ORDER BY myprice ASC[/code] This will NOT use any index you may have on `price`, so it will have to sort it every time you make the query. The best way to handle this is to change the column in mysql to a DECIMAL or FLOAT type.
  17. add new hits from Table A to existing ones in Table B [code]UPDATE B INNER JOIN A ON B.itemid=A.itemid SET B.hits = B.hits + A.hits[/code] INSERT new items/hits in table B that exist in table A but not B [code]INSERT INTO B (itemid, hits)     SELECT A.itemid, A.hits FROM A LEFT JOIN B ON A.itemid=B.itemid WHERE B.itemid IS NULL[/code] The first solution just uses a join in the update query, which is perfectly legal. The second solution uses the INSERT ... SELECT syntax. It also uses LEFT JOIN and IS NULL, so that it only selects rows from A that don't have a match in B.
  18. I can't be much more informative than the error you already got. COUNT(*) is a group function, it operates on lots of rows. You can't mix group functions and columns for which you want per-row detail. You are just trying to get a row count of your result, but that's why there is a mysql_num_rows() function (->RecordCount() with your database wrapper). Use that instead. [code]$get_items = "SELECT unique_id FROM items WHERE owner = '$cookie_userid' AND location = 'inventory' AND item_id = 22"; if ($db_result = $db_conn->Execute($get_items)) {     if ($db_result->RecordCount()) {         while (!$db_result->EOF) {             $wood_ids[] = $db_result->fields['unique_id'];         }         $wood_count = $db_result->RecordCount();     }     $db_result->Close(); } else     die('SQL Error #'.$db_conn->ErrorNo().': '.$db_conn->ErrorMsg());[/code]
  19. PHP doesn't have anything special for this. Use mysql_fetch_row() and get the results numerically. For instance: SELECT col1, col2, col3, col1 FROM table ... $row = mysql_fetch_row() ... $col1_firsttime = $row[0]; $col2 = $row[1]; $col3 = $row[2]; $col1_secondtime = $row[3];
  20. [code]SELECT last_name, first_name FROM names WHERE id IN ( select id from names order by id desc limit 10 ) ORDER BY last_name[/code]
  21. Where's your call to mysql_query()?
  22. Add an auto_increment column called `id` (or whatever you want), then you can do: "UPDATE table SET column='value' ORDER BY id DESC LIMIT 1"
  23. It's just "ORDER BY x ASC, y DESC"
  24. Put it in the WHERE clause: [code]//makes sure that the code is only executed when the option variable is present if (isset($_GET['cat_num'])) {    $query = 'SELECT cat_name FROM admin_cat WHERE cat_num="' . $_GET['cat_num'] . '"';    $results = mysql_query ($query);    $row = mysql_fetch_array ($results);    echo '<div class="title">' . $row['cat_name'] . '</div>'; } else {    echo '<div class="title">No category selected.</div>'; }[/code]
  25. I've never seen a problem that requires me to add tables dynamically, and I've seen a lot of problems. If you post some more details, we might be able to help you design something a little more stable. However, you can always use SHOW commands to get whatever you need. SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, etc.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.