Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,345

  • Joined

  • Last visited

  • Days Won

    795

 Content Type 

Posts posted by Barand

    if row exists update else insert

    in MySQL Help

    Posted

    Set a UNIQUE index on phone. Tablets can all have null values.

    EG

    CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(50) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `pin` int(11) DEFAULT NULL,
  `device` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_customers_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

mysql> select * from customers;
+----+----------+----------+------+--------+
| id | fullName | phone    | pin  | device |
+----+----------+----------+------+--------+
|  1 | Cust_1   | 12346789 | 1234 | phone  |
|  2 | Cust_2   | 12356789 | 3456 | phone  |
|  3 | Cust_3   | 12366789 | 5678 | phone  |
|  4 | Cust_4   | NULL     | NULL | tablet |
|  5 | Cust_5   | NULL     | NULL | tablet |
+----+----------+----------+------+--------+

INSERT INTO customers (fullname,phone,pin,device)
VALUES ('Cust_6', 12346789, 1010, 'phone')
ON DUPLICATE KEY UPDATE pin = 1010;

mysql> select * from customers;
+----+----------+----------+------+--------+
| id | fullName | phone    | pin  | device |
+----+----------+----------+------+--------+
|  1 | Cust_1   | 12346789 | 1010 | phone  |   <-- updated
|  2 | Cust_2   | 12356789 | 3456 | phone  |
|  3 | Cust_3   | 12366789 | 5678 | phone  |
|  4 | Cust_4   | NULL     | NULL | tablet |
|  5 | Cust_5   | NULL     | NULL | tablet |
+----+----------+----------+------+--------+
    • Like 1

    inserting using case else

    in MySQL Help

    Posted

    No, you are still pulling the a_id and h_id magically from nowhere.

     

    Try

    INSERT INTO match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)
SELECT 14,6,7,8,9, IFNULL(team_catid, 0)	
FROM
    (
    SELECT a_id as teamid
    FROM matches
    UNION
    SELECT h_id as teamid
    FROM matches
    ) teams
    LEFT JOIN teamcat tc USING (teamid);

    I'd be interested in seeing the entire data model for this application. It has a very weird feel to it.

    Implementing multiple incrementing keys in a table

    in MySQL Help

    Posted

    Make the primary key (pet_type, pet_id) where the pet_id is auto_increment. (This increments it within the pet type) EG

    mysql> CREATE TABLE `pet` (
    ->   `user_id` int(11) DEFAULT NULL,
    ->   `pet_type` varchar(45) NOT NULL,
    ->   `pet_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`pet_type`,`pet_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO pet (user_id, pet_type) VALUES
    -> (1, 'cat'),
    -> (2, 'cat'),
    -> (2, 'dog'),
    -> (3, 'cat'),
    -> (4, 'dog');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pet;
+---------+----------+--------+
| user_id | pet_type | pet_id |
+---------+----------+--------+
|       1 | cat      |      1 |
|       2 | cat      |      2 |
|       2 | dog      |      1 |
|       3 | cat      |      3 |
|       4 | dog      |      2 |
+---------+----------+--------+

    Drawing HTML Table With MySQL Data

    in PHP Coding Help

    Posted · Edited by Barand

    Two methods. Loop through the query results

     

    1 ) When the occupation changes, start a new table (hence the order by occupation)

     

    or

     

    2 ) store the results in an array indexed by occupation

     

    This is the second method (you may peep if you get stuck)

     

     

    $sql = "select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y' 
        order by foccup, sname";
$res = $db->query($sql);

#
# store data in arrays by occupation
#
$data = [];
foreach ($res as $student) {
    $data[$student['foccup']][] = $student;
}
#
# now loop through the array data
# creating your tables
#
foreach ($data as $occup => $students) {
    # new occupation table
    echo "<table border='1' style='border-collapse:collapse'>
            <tr><th colspan='2'>$occup</th></tr>
            <tr><th>Student Name</th><th>Fathers Name</th></tr>\n";
    # output student rows
    foreach ($students as $student) {
        echo "<tr><td>$student[sname]</td><td>$student[fname]</td></tr>\n";
    }
    echo "</table><br>\n";
}

     

     

     

    And this is the first method (again, no peeping)

     

     

    $sql = "select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y' 
        order by foccup, sname";
$res = $db->query($sql);

#
# process data, starting new table on change of occupation
#
$current_occupation='';
foreach ($res as $student) {
    if ($student['foccup'] != $current_occupation) {
        if ($current_occupation) {
            echo "</table><br>\n";  // end previous table
        }
        # start new table
        echo "<table border='1' style='border-collapse:collapse'>
            <tr><th colspan='2'>$student[foccup]</th></tr>
            <tr><th>Student Name</th><th>Fathers Name</th></tr>\n";
        $current_occupation = $student['foccup'];
    }
    echo "<tr><td>$student[sname]</td><td>$student[fname]</td></tr>\n";
}
echo "</table><br>\n";  // end final table

     

     

    Best guess date from messy string.

    in PHP Coding Help

    Posted · Edited by Barand

    Provided that you can spell "February", only two of those formats do not work

    $dates = [  'feb 21, 1999',
            'February 21, 1999',
            '02/21/99',
            '2/21/99',
            '99/2/21',
            '2-21-1999',
            '19990221',
            'sun, Feb 21, 1999',
            'Sunday February 21, 1999'
            ];

echo '<pre>';
foreach ($dates as $dstr) {
    printf("%-30s%-15s%s\n", 
                $dstr, 
                date('Y-m-d', strtotime($dstr)),
                strtotime($dstr)==0 ? 'X' : ''
                );
}
    Outputs

    feb 21, 1999                  1999-02-21     
February 21, 1999             1999-02-21     
02/21/99                      1999-02-21     
2/21/99                       1999-02-21     
99/2/21                       1970-01-01     X
2-21-1999                     1970-01-01     X
19990221                      1999-02-21     
sun, Feb 21, 1999             1999-02-21     
Sunday February 21, 1999      1999-02-21
    For rogue formats you can always use DateTime::createFromFormat()

    Get user position in highscore, PDO

    in PHP Coding Help

    Posted

    Have you tried it without the WHERE line? That will give all scores with ranks.

     

    Is $udid numeric? If not, it needs to be inside single quotes. (Really it shouldn't be in the query at all, it should be a prepared query with a placeholder for the udid parameter. But that's another lesson.

    Get user position in highscore, PDO

    in PHP Coding Help

    Posted · Edited by Barand

    You can do something like this

     

    my data

    mysql> select * from score;
+-------+-------+
| name  | score |
+-------+-------+
| Bob   |    75 |
| David |   106 |
| Jane  |    75 |
| Joe   |    61 |
| Mary  |    59 |
| Mike  |    61 |
| Sam   |    76 |
+-------+-------+
7 rows in set (0.00 sec)

    query

    SELECT a.name 
  , a.score
  , COUNT(b.name)+1 as rank
  , tot
FROM score a
  JOIN (SELECT COUNT(name) as tot FROM score) as total
  LEFT JOIN score b ON b.score > a.score
WHERE a.name = 'Jane'                                  -- OPTIONAL
GROUP BY a.name
ORDER BY a.score DESC;

+------+-------+------+-----+
| name | score | rank | tot |
+------+-------+------+-----+
| Jane |    75 |    3 |   7 |
+------+-------+------+-----+
×
×
  • 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.