Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,566

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    As I said in my reply, the query will return one row with the required count. You need to get the result from the total field returned in the query, not count the rows. $rQuery = mysql_query("SELECT COUNT(id) as total FROM " . $pre . $sqlgame . " WHERE (" . $sWhere . ")"); $row = mysql_fetch_assoc($rQuery); $iCount = $row['total']; Stop using the mysql_ functions (they will not be available in future versions of php) and start using mysqli_ functions or PDO.
  2. Barand
    Rather than fetch all the records just to see how many there are it would be more efficient to fetch 1 row with the desired count $rQuery = mysql_query("SELECT COUNT(id) as total FROM " . $pre . $sqlgame . " WHERE (" . $sWhere . ")");
  3. Barand
    It looks like x values have to be dates or datetimes for Morris time series plots, day numbers don't work. I think this should do it for the month chart $query = "SELECT SUM(cust_order_total) as daily_gross_sales, due_date as day FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY due_date ORDER BY due_date"; $rows = ''; $result = mysqli_query($connection,$query); $total_rows = mysqli_num_rows($result); if($total_rows > 0) { $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); echo json_encode($rows); } ?> <html> <head> <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css"> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script> <script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script> <script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script> <script type='text/javascript'> $().ready(function() { Morris.Line({ // ID of the element in which to draw the chart. element: 'month_chart', // Chart data records -- each entry in this array corresponds to a point // on the chart. data: <?php echo json_encode($rows);?>, // The name of the data record attribute that contains x-values. xkey: 'day', // A list of names of data record attributes that contain y-values. ykeys: ['daily_gross_sales'], // Labels for the ykeys -- will be displayed when you hover over the // chart. labels: ['Total Sales'], lineColors: ['#0b62a4'], xLabels: 'day', xLabelAngle: 45, // Disables line smoothing smooth: true, resize: true }); }) </script> </head> <body> <div id='month_chart' style="height: 500px; width: 800px"></div> </body> </html>
  4. Barand
    the query you are executing is $query = "SELECT cust_order_total,due_date FROM orders ORDER BY due_date";
  5. Barand
    When is "yyyy/mm/dd" ? You probably want three queries, on for each graph. Store results in array with date as the key and sales as the value 1 graph with the current month and daily sales(so this will correspond to days on the X axis and prices on the Y axis). SELECT SUM(sales_values) as dailysales, DAY(dates) as day FROM orders WHERE YEAR(dates) = YEAR(CURDATE()) AND MONTH(dates) = MONTH(CURDATE()) GROUP BY day ORDER BY dates 2 graph with the current week from Monday to Sunday and daily sales(so this will correspond to days on the X axis and prices on the Y axis). SELECT SUM(sales_values) as dailysales, WEEKDAY(dates) as day FROM orders WHERE YEARWEEK(dates) = YEARWEEK(CURDATE()) GROUP BY day ORDER BY dates 3 graph with the current Year and Weekly sales(so this will correspond to weeks on the X axis and prices on the Y axis). SELECT SUM(sales_values) as weeklysales, WEEK(dates) as weekno FROM orders WHERE YEAR(dates) = YEAR(CURDATE()) GROUP BY weekno ORDER BY dates
  6. Barand
    Also note that you do not have an input in your form with the name "tracking", so $_POST['tracking'] will not exist.
  7. Barand

    Servo calling!

    Barand replied to servo's topic in Introductions

    Who's there?
  8. Barand
    try using different variable names for the input and the array.
  9. Barand
    For now, from your current query, I'll assume the data looks something like this +------------------------------------------------+ +-----------------------------------+ | Table bids | | Table bid_incrementss | +--------+---------+-------+---------------------+ +------------+----------+-----------+ | bid_id | item_id | price | created_timestamp | | price_from | price_to | increment | +--------+---------+-------+---------------------+ +------------+----------+-----------+ | 1 | 3 | 10.00 | 2015-08-29 12:18:56 | | 0.00 | 9.99 | 2.00 | | 2 | 2 | 20.00 | 2015-08-29 12:18:56 | | 10.00 | 19.99 | 3.00 | | 3 | 4 | 30.00 | 2015-08-29 12:18:56 | | 20.00 | 29.99 | 4.00 | +--------+---------+-------+---------------------+ | 30.00 | 99.99 | 5.00 | +------------+----------+-----------+ using this query SELECT CASE WHEN b.price IS NULL THEN (SELECT MAX(increment) FROM bid_increments) ELSE (b.price + bi.increment) END AS `minimum_bid` FROM bid_increments bi LEFT JOIN bids b ON b.price BETWEEN bi.price_from AND bi.price_to AND b.item_id = 1 ORDER BY item_id DESC LIMIT 1 the results are item_id = 1 item_id = 2 item_id = 3 item_id = 4 +-------------+ +-------------+ +-------------+ +-------------+ | minimum_bid | | minimum_bid | | minimum_bid | | minimum_bid | +-------------+ +-------------+ +-------------+ +-------------+ | 5.00 | | 24.00 | | 13.00 | | 35.00 | +-------------+ +-------------+ +-------------+ +-------------+
  10. Barand
    Your first task is create a select query with the correct syntax instead of the rubbish above. Look at the order of the statement parts in your other select queries that you have posted, and the use of important keywords, like WHERE. When you process the query, store in an array then json_encode() the array. What will you show on the graph when you have multiple values for the same date?
  11. Barand
    Can we see some data so we know what we are dealing with?
  12. Barand
    Instead of just resurrecting a five year old topic, why don't you read it too. The answer is there.
  13. Barand
    $info6 = "skap1'; ^ ^ | | notice something odd?
  14. Barand
    You get that error because the string index "color" is not in quotes $color = $_POST[color]; should be $color = $_POST['color']; Because you haven't used quotes, php thinks it is a defined constant. When it cannot find the definition it throws the error.
  15. Barand
    Uploading is done once, viewing is done many times. It's a better use of resources to resize the once rather than the many, especially if you have a gallery page serving up, say, 20 images per page when it could have a significant impact on the page load time.
  16. Barand
    $email is a string value and therefore needs to be in single quotes in your queries "INSERT INTO betakey (Mail) VALUES ('$Mail')" "SELECT (Mail) FROM betakey WHERE Mail = '$Mail' " It would make more sense to do the check with the SELECT query before inserting. You could just use the INSERT query if you define email as unique and do away with the select. Then, if you get a duplicate key error, you know it already existed.
  17. Barand
    $result = $db->query("SELECT something FROM somewhere");
  18. Barand
    If you are using ODBC, why suddenly attempt to use mssql_num_rows() instead of odbc_num_rows()?
  19. Barand
    try $SQL = " SELECT sr.review_id, sr.review_date, sr.review, sr.reviewer_name, sr.reviewer_surname, r.rating_name, r.rating FROM school_reviews sr LEFT JOIN ratings r USING(review_id) WHERE sr.active = 1 AND sr.is_deleted = 0 AND sr.school_id = $school_id ORDER BY sr.review_date DESC"; $q->query($DB,$SQL); // store results of query in array $reviews = array(); $totals = array(); // ADDED $ratingsCount = 0; // ADDED while($row = $q->fetch_assoc()) { // get the review id $id = $row['review_id']; // group reviews by review id if(!isset($reviews[$id])) { $reviews[$id] = array( 'message' => $row['review'], 'date' => $row['review_date'], 'name' => $row['reviewer_name'], 'surname' => $row['reviewer_surname'], 'ratings' => array() ); } // group ratings by review id $reviews[$id]['ratings'][] = array( 'name' => $row['rating_name'], 'value' => str_repeat('* ', $row['rating']) #'value' => '<img src="/en/images_v2/ratings/star'.$row['rating'].'.png" />' ); // rating totals // ADDED $ratingsCount++; if (isset($totals[$row['rating_name']])) { $totals[$row['rating_name']] += $row['rating']; } else { $totals[$row['rating_name']] = $row['rating']; } } // loop over the reviews foreach($reviews as $review_id => $review) { // output review echo "<div class=\"schoolreview\">"; echo "<p><span class=\"right\">Posted On: " . $review['date'] . "</span>"; echo "Review by: ".$review['name']."<br />\n" . "</p>\n"; // output ratings list for each review echo "<ul class=\"ratings\">\n"; foreach($review['ratings'] as $rating) { echo "\t<li>" . $rating['name'] . ': ' . $rating['value'] . "</li>\n"; } echo "</ul>\n"; echo "<p>Review: " . nl2br($review['message']) . "</p>"; echo "</div>"; } // loop over the totals // ADDED $reviewCount = count($reviews); echo "Average ratings\n<ul>\n"; foreach ($totals as $name => $tot) { $avg = number_format($tot/$reviewCount, 2); echo "<li>$name : $avg</li>\n"; } echo "</ul>\n"; $overallAverage = number_format(array_sum($totals)/$ratingsCount, 2); echo "Overall average rating : $overallAverage";
  20. Barand
    What is the query that is being executed?
  21. Barand
    You don't close the category select until after you close the form. Move the </select> to a position before the submit button +-------> | ^ <input type='submit' name='submit' value='Search' ></br></br></br></center> | </form> | +--<-- </select>
  22. Barand
    An alternative would be only to get the file contents when there is a new id value $prevID = ''; foreach($result as $row){ // set replacement to be the string from db if ($row['id'] != $prevID { $str = file_get_contents("templates/staff.html", true); $prevID = $row['id']; } $replace = $row['content']; $srch = $row['location']; $str = $this->replace($srch, $replace, $str); }
  23. Barand
    You have to define for what you want the average eg average rating for each review SELECT review_id, AVG(rating) from tablename GROUP BY review_id average rating for each rating_name SELECT rating_name, AVG(rating) from tablename GROUP BY rating_name average for the whole table SELECT AVG(rating) from tablename
  24. Barand
    I gave you the answer to that:
  25. Barand
    JOIN and FOREIGN KEY are not mutually exclusive, it isn't a case of one or the other. +------------------+ +-----------------+ | reseller | | order | +------------------+ +-----------------+ | reseller_id PK | ---+ | order_id PK | | reseller_name | | | order_total | | reseller_surname | +---< | reseller_id FK | +------------------+ +-----------------+ In the tables above, reseller_id is is the primary key (PK) of the reseller table. It also appears in the order table to relate an order to the reseller. As it is the PK of one table appearing in another table it is, by definition, a FOREIGN KEY. When querying the tables you (normally) JOIN the tables using this PK --> FK relationship. You also have the option of applying FOREIGN KEY CONSTRAINTS on a table. If you do this enforces "referential integrity" so you cannot add an order with a reseller_id that does not exist in the reseller table, or you cannot delete a reseller record that has related orders in the order table.
×
×
  • 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.