Jump to content

bubblegum.anarchy

Members
  • Posts

    526
  • Joined

  • Last visited

    Never

Posts posted by bubblegum.anarchy

  1. This is a fun query to create:

    SELECT
    ProductID
    , group_concat(DISTINCT subjects.Name SEPARATOR '\n') AS subject_names
    , group_concat(DISTINCT grades.Name SEPARATOR '\n') AS grade_names
    , products.Name
    FROM products
    INNER JOIN subjects ON find_in_set(subjects.SubjectID, products.SubjectID)
    INNER JOIN grades ON find_in_set(grades.GradeID, products.GradeID)
    WHERE ProductID = 84512
    GROUP BY ProductID
    

  2. Consider reading up on session handling from the PHP manual, the following is a basic session handling extract:

     

    Example 1860.  Registering a variable with $_SESSION.

    <?php
    session_start();
    // Use $HTTP_SESSION_VARS with PHP 4.0.6 or less
    if (!isset($_SESSION['count'])) {
       $_SESSION['count'] = 0;
    } else {
       $_SESSION['count']++;
    }
    ?>
    

    Example 1861.  Unregistering a variable with $_SESSION and register_globals disabled.

     

    <?php
    session_start();
    // Use $HTTP_SESSION_VARS with PHP 4.0.6 or less
    unset($_SESSION['count']);
    ?>
    

  3. This might look similar to a recent thread where I requested a query to translate `Second Monday March`...

     

    I expect `Second Monday March` to return null but I do not expect the same result for the 1st Jan and 26th Jan... anyways.. I have totally flagged the str_to_date function... now the values are being converted in php first since there are not many public holiday dates and I can apply the array in static form to a query using IN ().

  4. The InnoDB storage engine is required to implement foreign keys to providing the cascading effect... but the explicit definition of a foreign key is not required if you simply want to link two tables via a reference id, just be sure to index the referencing id and you can use the default low memory myISAM storage engine.

  5. Not one query but maybe some better ideas can be gleamed from the following:

    <?php
    $request_id = 5
    $chunk_count = 5
    
    $result = mysql_query("
       SELECT IF(".$chunk_count." - count(*) < 0, 0, ".$chunk_count." - count(*)) 
       FROM tablename 
       WHERE id >= ".$request_id." 
       ORDER BY id");
    
    list($limit_count) = mysql_fetch_row($result);
    
    $result = mysql_query("
       ( SELECT * FROM tablename WHERE id >= ".$request_id." ORDER BY id LIMIT ".$chunk_count." )
      UNION 
       ( SELECT * FROM tablename ORDER BY id LIMIT ".$limit_count." )
    ");
    ?>
    

  6. use mysql_insert_id() to get the last insert id

     

    <?php
    $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
    if (!$link) {
       die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('mydb');
    
    mysql_query("INSERT INTO mytable (product) values ('kossu')");
    printf("Last inserted record has id %d\n", mysql_insert_id());
    ?>
    

  7. string mysql_field_name ( resource result, int field_offset )

     

    <?php
    /* The users table consists of three fields:
    *   user_id
    *   username
    *   password.
    */
    $link = @mysql_connect('localhost', 'mysql_user', 'mysql_password');
    if (!$link) {
       die('Could not connect to MySQL server: ' . mysql_error());
    }
    $dbname = 'mydb';
    $db_selected = mysql_select_db($dbname, $link);
    if (!$db_selected) {
       die("Could not set $dbname: " . mysql_error());
    }
    $res = mysql_query('select * from users', $link);
    
    echo mysql_field_name($res, 0) . "\n";
    echo mysql_field_name($res, 2);
    ?>
    

    The above example will output:

     

    user_id
    password
    

     

    and use int mysql_num_fields ( resource result ) for a field count

  8. The str_to_date function is driving me nuts.. working one minute and not working the next (returns null when not supposed to).

     

    I have a simple public_holiday table with an `occurs` field of type varchar(255) not null default '1st January';

     

    I have tried all manner of string data types and even setting the field to allow null but the function remains sporatic

     

    the query used is:

    SELECT str_to_date(occurs, '%D %M') FROM public_holiday; #works one day and returns null the next

    SELECT str_to_date('1st January', '%D %M') from public_holiday; #always works

     

    table contains:

    1st January

    26th January

    Second Monday March

     

    Before I give up on the function completely, does anyone know what might be causing the null returns?

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