Jump to content

Canman2005

Members
 View Profile  See their activity

  • Posts

    669

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by Canman2005

    Query Order

    in PHP Coding Help

    Posted

    Well no, the order by position should remain, I just wanted to put at the top of the results the rows with dates that have passed in the last 15 days. So if ROW 1 has a date of 2008-09-01 and ROW 2 has a date of 2008-10-25 then ROW 1 would be at the top because its date field passed less than 15 days ago. But then keeping the ORDER BY `position` for the rest of the results.

     

    If that makes sense? :)

    Query Order

    in PHP Coding Help

    Posted

    Sure

     

    My `products` table looks like

     

    CREATE TABLE `products` (
  `id` int(10) NOT NULL,
  `title` varchar(255) NOT NULL,
  `position` int(10) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `products` (`id`, `title`, `position`, `date`) VALUES
(1, 'HotDog', 2, '2008-04-20'),
(2, 'Burger', 4, '2008-10-24'),
(3, 'Chips', 1, '2008-10-31'),
(4, 'Drinks', 3, '2008-10-02');

     

    My current QUERY looks like

     

    SELECT * FROM `test` WHERE date <= '2008-10-29' ORDER BY `position` ASC

     

    which gets me

     

    1  HotDog  2  2008-04-20

    4 Drinks 3 2008-10-22

    2 Burger 4 2008-10-24

     

    What I want to do is change the QUERY so that the first rows reurned would be any rows which have  `date` that started 15 days ago.

     

    Row 4 has a date of 2008-10-22 and row 2 has a date 2008-10-24, therefore those dates passed less than 15 days of todays date (if that make sense), therefore they would appear at the top of the results.

     

    The end result would look something like

     

    2 Burger 4 2008-10-24

    4 Drinks 3 2008-10-22

    1  HotDog  2  2008-04-20

     

    Row 1 has a date of 2008-04-20, therefore it's not within 15 days of todays date, therefore it would just return it's self in a `position` order at the bottom.

     

    As you will noticed, the order of rows 1 and 4 are in date order showing the one with the closest date to today, at the top.

     

    Does that make sense?

     

    Can anyone help?

     

    Thanks

    Query Order

    in PHP Coding Help

    Posted

    Hi all

     

    I have a database with products in it and in each row is a field called "dateadded", in that field I store dates when products were added, such as '2008-08-31'.

     

    Is there a way to run a QUERY which returns all rows in the database but places at the top, the rows which were added 15 days ago with newest added at top and then the rest of the rows that were added more than 15 days ago ordered by `product_title`

     

    Is there a way to QUERY that?

    [SOLVED] Cost per weight calc

    in PHP Coding Help

    Posted

    Hi all

     

    Wondering if someone can help.

     

    I have a few variables, they are

     

    //weight in grams

    $weight = '600';

    $price = '299.99';

     

    //weight in kilograms (kg)

    $weight = '45.2';

    $price = '699.21';

     

    I need to do a calculation to work out the cost per kilogram (kg) for both my grams variables and also the kilogram (kg) variables

     

    What would be the best way to find out the cost per kilogram (kg)

     

    Any help would be ace

     

    thanks

    Datatype for storing Money in MySQL

    in PHP Coding Help

    Posted

    Hey Canman2005,

    Can you calculate totals if the amount is VARCHAR. Say in a shopping cart.

     

    I believe so, I have never had any issues with using it, mostly because I have had to take existing large excel databases and don't want to chance messing up any money values, but then im not much of an expert and im sure other suggestions would be better, but personally I would stick with VARCHAR

    Sending BULK Emails

    in PHP Coding Help

    Posted

    Hi all

     

    I wondered if someone can give me some advice.

     

    Basically I have this little newsletter system, I do a PHP QUERY to return all rows in a newsletter table and then run a PHP mail for each row returned.

     

    I have started to get a few people signing up and recently I ran my script and my host moaned that I used lots of bandwidth and tied the server up, he suggested I get the script to send out 10 mails every 5 minutes to avoid this happening.

     

    How could I get my script to just return 5 rows, wait 5 mins and then return another 5 rows and then repeat that again until no more rows are returned.

     

    Can anyone give some good advice?

     

    Thanks

     

    Dave

    [SOLVED] Help with QUERY

    in MySQL Help

    Posted

    Thanks, but that seemed to return incorrect too.

     

    Here is some sample data

     

    -----------------The Allergies Products Table ----------------------------

     

    CREATE TABLE IF NOT EXISTS `allergies_products` (

      `id` int(4) NOT NULL auto_increment,

      `product_id` int(10) NOT NULL,

      `allergy_id` int(10) NOT NULL,

      PRIMARY KEY  (`id`)

    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

     

    INSERT INTO `allergies_products` (`id`, `product_id`, `allergy_id`) VALUES

    (5, 1, 13),

    (4, 1, 28),

    (6, 1, 31),

    (7, 2, 30),

    (8, 3, 31);

     

    -----------------The Products Table ----------------------------

     

    CREATE TABLE IF NOT EXISTS `products` (

      `id` int(4) NOT NULL auto_increment,

      `category` int(4) NOT NULL default '0',

      `title` varchar(255) NOT NULL default '',

      `image_1` int(4) default NULL,

      `description` text,

      `live` int(1) NOT NULL default '0',

      PRIMARY KEY  (`id`)

    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

     

    --

    -- Dumping data for table `products`

    --

     

    INSERT INTO `products` (`id`, `image_1`, `title`, `description`, `price`, `live`, `category`) VALUES

    (1, 15, 'Burger', '', 2.99, 1, 1),

    (2, 12, 'HotDog', '', 2.99, 1, 1),

    (3, 15, 'Sausage Rolls', '', 2.99, 1, 1);

     

     

     

    So as you can see, product ID 1 is listed 3 times in the `allergies_products` table, so with my QUERY, if you selected NOT to rerturn any rows with the value 31 in the `allergy_id` field, then the results would only be

     

    (7, 2, 30)

     

    because `allergy_id` 31 is lined to `product_id` 1 and 3, therefore it shouldnt return any rows which have ID 1 or 3 in them.

     

    Does that make any kind of sense?

     

    Thanks very much

    [SOLVED] Help with QUERY

    in MySQL Help

    Posted

    Hi all

     

    I really need a hand, im getting into slightly complext QUERIES and really could do with some help.

     

    Basically I have the following QUERY

     

    SELECT p.id, p.image_1, p.title, p.description, p.price, p.live , p.category, l.product_id, l.allergy_id FROM products p LEFT JOIN allergies_products l ON (p.id = l.product_id) WHERE p.live = 1 AND p.category = '15' AND l.allergy_id !=17 GROUP BY l.product_id ORDER BY p.title ASC

     

    This gets all products from a main `products` table, but ignores any rows which are linked in the `allergies_products` table which have a `allergy_id` of 17 (or whatever the variable is)

     

    inside the table `allergies_products` I store;

     

    id

    product_id

    dietary_id

     

    although my QUERY is looking anything without `l.allergy_id !=17` it could be that the same product ID is listed twice in the `allergies_products` table but with different `allergy_id` values.

     

    How can I get the QUERY to ignore any rows with a `allergy_id` value of 17 (like it currently does), but then also ignore all rows that have the same `product_id` value as the rows don't have an `allergy_id` value of 17.

     

    Does that make any sense?

     

    Please can some help me? You're my only hope

     

    Thanks in advance :'(

    Ajax Loader Issue

    in Javascript Help

    Posted

    Hi all

     

    I'm using the following script to pull the contents of a page into a SPAN tag

     

    function ajaxLoader(url,id) {
  if (document.getElementById) {
    var x = (window.ActiveXObject) ? new ActiveXObject("Microsoft.XMLHTTP") : new XMLHttpRequest();
  }
  if (x) {
    x.onreadystatechange = function() {
el = document.getElementById(id);
        el.innerHTML = "";

      if (x.readyState == 4 && x.status == 200) {
        el = document.getElementById(id);
        el.innerHTML = x.responseText;
      }
    }
    x.open("GET", url, true);
    x.send(null);
  }
}

     

    the SPAN is created with

     

    <span id="myspan"></span>

     

    and then I do the link to pull in content using

     

    <a onclick="ajaxLoader('mypage.php','myspan');">

     

    This works great as a normal text link and in FireFox I don't get any problems using it anywhere.

     

    The issue I have is using it in a list menu in IE, if I apply the onclick to

     

    <select name="list" onmousedown="ajaxLoader('mypage.php','myspan');">

    <option value="">Option 1</option>

    <option value="">Option 2</option>

    </select>

     

    then it works fine, but if I apply it like

     

    <select name="list">

    <option onmousedown="ajaxLoader('mypage.php','myspan');" value="">Option 1</option>

    <option onmousedown="ajaxLoader('mypage.php','myspan');" value="">Option 2</option>

    </select>

     

    then it refuses to work.

     

    Any ideas why issues in IE?

     

    Help :(

     

    Thanks

     

    Ed

    QUERY Help - Crossing Data on 2 Tables

    in PHP Coding Help

    Posted

    Hi all

     

    Really really need someones help.

     

    I am having an issue puttrng a QUERY togther.

     

    Let me firstly explain my tables, I have two tables called "products" and also "linked_products", I have put a brief export below

     

     

    products

     

    ID    TITLE

    1    chips

    2    burgers

    3    drinks

     

     

    linked_products

     

    ID  PRODUCT_ID  TYPE

    1    2                  3

    2    2                  4

    3    1                  3

     

     

    So as you can see, there is a field called "PRODUCT_ID" in the table called "linked_products", that field refers to the ID field held in "products" table.

     

    As you will also see, row 2 in the "products" table (burgers) is listed twice in the "linked_products" table assigned to "TYPE" 3 and 4.

     

    Product ID 1 (chips) is assigned to "TYPE" 6 in the "linked_products" table.

     

    You will notice that ID 3 of the "products" table (drinks) is now listed in the "linked_products" table at all.

     

    What I need to do is run a QUERY which gets all rows from the "products" table excluding any rows which are listed under the "PRODUCT_ID" field in the "linked_products" that have a "TYPE" equaling 3 for example, not returning any with "TYPE" defined as 3 would just return ROW 3 (drinks), this is because ROW 3 (drinks) does not appear in the "linked_products" table with a value of 3.

     

    If we were to do it again but asking for any which have a "TYPE" as 4, then it would return just ROW 1 (chips) and 3 (drinks) this is because ROW 1 and ROW 3 of the "products" table don't have a value of 4 under the "TYPE" field in the "linked_products" table, only ROW 2 has a field value of 4.

     

    Tried to explain this the best I can, if anyone can help, that would be fantastic as im hitting my head harder ansd harder lol

     

    Thanks

     

    Ed

    [SOLVED] printing word every 4th row returned

    in PHP Coding Help

    Posted

    Hi all

     

    I have a very simple QUERY, looks like

     

    <?php
$sql = "SELECT * FROM `users` WHERE `area` = '99'";
$show = @mysql_query($sql,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($show))		
{
print $row['fullname'];
print "<br>";
}
?>

     

    This returns a list like

     

    David

    Harry

    Sarah

    Richard

    Shelly

    Bob

    Jerry

    Vicki

    Shaun

     

    How is it possible to insert the word "NEXT" after every 4th row returned, so the above output would look like

     

    David

    Harry

    Sarah

    Richard

    NEXT

    Shelly

    Bob

    Jerry

    Vicki

    NEXT

    Shaun

     

    Any help would be great

     

    Thanks

     

    Ed

×
×
  • 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.