Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,425

  • Joined

  • Last visited

  • Days Won

    806

 Content Type 

Posts posted by Barand

    Creating executable sql code from html tables

    in PHP Coding Help

    Posted

    P.S.

    Some of the data is unsuitable for data storage purposes and needs to be reformatted before storing in a database table 

    +------------+--------------------------------+----------------+----------------------------------+
| Column     |        WRONG                   |   CORRECT      |  Comments                        |
+------------+--------------------------------+----------------+----------------------------------+
| Age        | 31                             |  Not required  |  DoB tells you the age           |
| DoB        | 3/4/1988 (31 years old)        |  1988-04-03    |  Store in correct date format    |
| Height     | 194 cm                         |  194           |  Store unformatted numeric value |
| Weight     | 83 kg                          |  83            |  Store unformatted numeric value |
| Expires    | 30/6/2022                      |  2022-06-30    |  Store in correct date format    |
| Wage       | £25,000 p/w                    |  25000         |  Store unformatted numeric value |
| Value      | £12.75M                        |  12750000      |  Store unformatted numeric value |
+------------+--------------------------------+----------------+----------------------------------+

     

    Creating executable sql code from html tables

    in PHP Coding Help

    Posted · Edited by Barand

    Try using domDocument or simple_html_dom.

    EG 

    include("/path/to/simple_html_dom.php");

$html = file_get_html('my_source.html');

$rows = $html->find('tr[bgcolor="#EEEEEE"]');

$csvFile = fopen('mydata.csv', 'w');
foreach ($rows as $row) {
    $data = [];
    foreach ($row->children() as $itm) {
        $data[] = $itm->plaintext;
    }
    fputcsv($csvFile, $data);
}
fclose($csvFile);

    The csv file can then be loaded into a sql table using a LOAD DATA INFILE statement.

    • Like 1

    Database rsults formatting:

    in PHP Coding Help

    Posted

    You have taken the "|" characters out! How do expect to split it?

    The echoed string should look like 

    guid|username|health|...|misses

    I.E. 

    $row = [ 'guid' => 'xyz',
         'username' => 'fred',
         'health' => 1, 
         'level' => 42
       ];
       
echo $row['guid'] . '|' . $row['username'] . '|' . $row['health'] . '|' . $row['level'];

    or (easier if you want every $row item in their natural ORDER) 

    echo join('|', $row);

    Then when you split it, the array will look like 

    Array
(
    [0] => xyz
    [1] => fred
    [2] => 1
    [3] => 42
)

     

    Database rsults formatting:

    in PHP Coding Help

    Posted

    If you are splitting on "|", why put the PHP_EOLs in there?

    Also, try omitting the leading "|" in your string, so you have 

    guid|username|health|...|misses

    When you split that on "|" the guid should be item 0.

    With the leading "|" (i.e "| guid | username | …") I would expect item 0 to be blank after the split and guid would be item 1..

    Looping through MySQL table columns

    in PHP Coding Help

    Posted

    6 minutes ago, Heartstrings said:

    What do you think about this php script to store this result in an array?

    I'm afraid that my answer is "Not a lot".

    • Grouping by appointment_id will give you one row in the result set for each appointment. The contents of fields other than the appointment_id will be arbitrary.
    • Don't use "select * ", define the columns you need.
    • That query doesn't require preparing (no user data values) and executing. You could just use $dbh->query($q).

    Looping through MySQL table columns

    in PHP Coding Help

    Posted

    A good source for php/MySQL interaction (using PDO is recommended) is https://phpdelusions.net/pdo_examples

    This query should get you started 

    SELECT 
     , scheduled_student_service
     , category
     , COUNT(*) as total_appointments
     , SUM(is_no_show) as no_shows
     , SUM(is_cancelled) as cancellations
FROM thetable
GROUP BY scheduled_student_service, category

    If that isn't what you want, repost with more details.

    In future, please don't post images of data. They are as much use as chocolate teapots if we need to recreate your data to test with when helping.

    • Like 1

    Dynamic Form Save

    in PHP Coding Help

    Posted

    Your form should contain a hidden field for the clientID then three selects for each engineer, indexed by the engineers' ids 

    hidden name=clientID value=$clientID

For each engineer:

    select name=job[$engID]
    select name=company[$engID]
    select name=site[$engID]

    To process the POST data 

    foreach ($_POST['job' as $engID => $job) {
    $company = $_POST['company''][$engID'];
    $site = $_POST['site'][$engID];

    // update database with the engineer, job, company, site values
}

     

    Select closest match from database

    in MySQL Help

    Posted

    The first record should always be the best match so you could add "LIMIT 1' to the end of the query and just process the single result. Check the values in the record and take the appropriate action. However, I don't now how your process operates so I don't know how significant other records in the results are if the first does not adequately suit the order.

    Html responsive for mobile device

    in HTML Help

    Posted · Edited by Barand

    Perhaps something without all those non-breaking spaces 

    <!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta http-equiv="Lang" content="en">
<title>Example</title>
<style type="text/css">
    table   { width: 80%; margin: 20px auto; font-family: calibri, sans-serif;}
    caption { border-bottom: 1px solid gray; }
    th      { text-align: left; padding: 8px 2px; color: #999;}
    td      { color: #25b0e9; padding: 8px 4px; }
</style>
</head>
<body>
    <table>
        <caption>Channel State</caption>
        <tr>
            <th>&nbsp;</th>
            <th>C1</th>
            <th>C2</th>
            <th>C3</th>
            <th>C4</th>
            <th>C5</th>
            <th>C6</th>
            <th>C7</th>
            <th>C8</th>
        </tr>
        <tr>
            <th>Left (+)</th>
            <td>&check;</td>
            <td>&check;</td>
            <td>&check;</td>
            <td>&nbsp;</td>
            <td>&check;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
        </tr>
        <tr>
            <th>Left (-)</th>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&check;</td>
            <td>&check;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
        </tr>
    </table>
</body>
</html>

    image.png.de4c19dca50b68b192fe07ff2dd0a2a6.png

    image.thumb.png.9c05ebb51b85ee041584a2908f7ff72e.png

    • Thanks 1

    Select closest match from database

    in MySQL Help

    Posted

    As you test data contained only a menu that was a perfect match to the test order and a menu that contained everything I added a couple of extra menus and orders. 

    SELECT m.menu_id
     , m.menu_name
     , COUNT(*) as matched
     , ROUND(count(*)/menuitems*100, 1) as `%menu`
     , ROUND(count(*)/orderitems*100, 1) as `%order`
FROM 
        ssm_menu m
    INNER JOIN 
        ssm_menu_connection c
            ON c.menu_id = m.menu_id
    INNER JOIN
        ssm_menu_order o
            ON o.menu_item_id = c.menu_item_id
    INNER JOIN
        (
            SELECT job_id
                , COUNT(DISTINCT menu_item_id) as orderitems
            FROM ssm_menu_order
            GROUP BY job_id
        ) jtot  ON jtot.job_id = o.job_id
    INNER JOIN
        (
            SELECT menu_id
                , COUNT(DISTINCT menu_item_id) as menuitems
            FROM ssm_menu_connection
            GROUP BY menu_id
        ) mtot ON m.menu_id = mtot.menu_id
WHERE o.job_id = 27
GROUP BY m.menu_id
ORDER BY matched DESC, `%menu` DESC, `%order` DESC;
+---------+----------------------------------------------------------+---------+-------+--------+
| menu_id | menu_name                                                | matched | %menu | %order |
+---------+----------------------------------------------------------+---------+-------+--------+
|       1 | Menu One                                                 |       3 | 100.0 |  100.0 |
|       2 | Private Dinner Party/Wedding - 3 Course Fine Dining Menu |       3 |  18.8 |  100.0 |
|       3 | Menu Three                                               |       2 |  66.7 |   66.7 |
|       4 | Menu Four                                                |       2 |  50.0 |   66.7 |
|       5 | Menu Five                                                |       1 |  25.0 |   33.3 |
+---------+----------------------------------------------------------+---------+-------+--------+

     

    • Great Answer 1

    Select closest match from database

    in MySQL Help

    Posted

    Thanks, that loaded OK. I'll look at your problem, which I interpret as ranking the menus by percentage of content of the ordered items.

    I expect there could be a situation where

    • items A,B,C,D,E are ordered
    • menu 1 contains A,B,C.
    • menu 2 contains B,C,D
    • menu 3 contains C,D,E

    ie No single menu contains all items ordered?

    How to display information on another page

    in PHP Coding Help

    Posted

    12 minutes ago, Skittle said:

    Um okay very helpful information but it doesnt answer my question

    The question has been answered as detailed as we can from the information provided.

    3 hours ago, mac_gyver said:

    since you haven't shown us  what the build_listing_card() code is or what output it produces, cannot help you with what or why your view listing links should be or don't work

    5 hours ago, requinix said:

    Use the query string and $_GET.

    10 hours ago, chhorn said:

    You are fetching $_GET['listing_id'] from the URL, so you have to provide that info at least in a link

     

     

    disable button if value exists or equal to array

    in PHP Coding Help

    Posted · Edited by Barand

    Here's a simplified example 

    $uid = 2;

$res = $bdd->prepare("SELECT f.id
                           , f.name
                           , u.file_id
                      FROM files f 
                             LEFT JOIN
                           user_files u 
                                ON f.id = u.file_id
                                AND u.user_id = ?
                      ORDER BY f.id
                    ");
$res->execute([$uid]);

$tdata = '';
foreach ($res as $row) {
    $disabled = $row['file_id'] ? 'disabled' : '';
    $tdata .= "<tr>
                 <td>{$row['id']}</td>
                 <td>{$row['name']}</td>
                 <td><button $disabled>Download</button></td>
               </tr> 
              ";
}
?>
<html>
<head>
<title>Example</title>
</head>
<body>
    <table>
        <?=$tdata?>
    </table>
</body>
</html>

     

    undefined index on check box

    in PHP Coding Help

    Posted

    Only select check boxes (and radio buttons) are sent in the form data so you need you check if they are present using isset().

    Example 

    <?php
   for ($i=1; $i<=5; $i++) {
       if (isset($_GET['mycb'][$i])) {
           echo "Check box $i selected<br>" ;
       }
   }
?>
<html>
<head>
<title>Example</title>
</head>
<body>
<hr>
   <form>
       Check box 1 : <input type="checkbox" name="mycb[1]" value="1"><br>
       Check box 2 : <input type="checkbox" name="mycb[2]" value="2"><br>
       Check box 3 : <input type="checkbox" name="mycb[3]" value="3"><br>
       Check box 4 : <input type="checkbox" name="mycb[4]" value="4"><br>
       Check box 5 : <input type="checkbox" name="mycb[5]" value="5"><br><br>
       <input type="submit" name="btnSub" value="Submit">
       
   </form>
</body>
</html>

     

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