PHP - Compare Two Values In Same Table

Hi,

My company has 240+ locations and as such some users (general managers) cover multiple sites.  When I run a query to pull user information, when the user has multiple sites to his or her name, its adds the second / third sites to the next columns, rather than wrapping it inside the same table cell.  It also works the opposite way, if a piece of data is missing in the database and is blank, its pull the following columns in.  Both cases mess up the table and formatting.  I'm extremely new to any kind of programming and maybe this isn't the forum for this question but figured I'd give it a chance since I'm stuck.

The HTML/PHP code is below:

<table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%">

                                        <thead>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                                <th>Actions</th>

                                            </tr>

                                        </thead>

                                        <tbody>

                                            <?php

                                            //QUERY TO SELECT ALL USERS FROM DATABASE

                                            $query = "SELECT * FROM users";

                                            $select_users = mysqli_query($connection,$query);

                                            

                                            // SET VARIABLE TO ARRAY FROM QUERY

                                            while($row = mysqli_fetch_assoc($select_users)) {

                                                $user_id = $row['user_id'];

                                                $user_firstname = $row['user_firstname'];

                                                $user_lastname = $row['user_lastname'];

                                                $username = $row['username'];

                                                $user_phone = $row['user_phone'];

                                                $user_image = $row['user_image'];

                                                $user_title_id = $row['user_title_id'];

                                                $user_role_id = $row['user_role_id'];

                                            

                                                // POPULATES DATA INTO THE TABLE

                                                echo "<tr>";

                                                echo "<td>{$user_id}</td>";

                                                echo "<td>{$user_firstname}</td>";

                                                echo "<td>{$user_lastname}</td>";

                                                echo "<td>{$username}</td>";

                                                echo "<td>{$user_phone}</td>";

                                            

                                                //PULL SITE STATUS BASED ON SITE STATUS ID

                                                $query = "SELECT * FROM sites WHERE site_manager_id = {$user_id} ";

                                                $select_site = mysqli_query($connection, $query);

                                                while($row = mysqli_fetch_assoc($select_site)) {

                                                    $site_name = $row['site_name'];

                                                    echo "<td>{$site_name}</td>";

                                                }

                                                echo "<td>{$user_title_id}</td>";

                                                echo "<td>{$user_role_id}</td>";

                                                echo "<td class='table-action'>

                                                    <a href='#'><i class='align-middle' data-feather='edit-2'></i></a>

                                                    <a href='#'><i class='align-middle' data-feather='trash'></i></a>

                                                    </td>";

                                                //echo "<td><a href='users.php?source=edit_user&p_id={$user_id}'>Edit</a></td>";

                                                echo "</tr>";

                                            }   

                                            ?>

 

                                            <tr>

                                                <td>ID</td>

                                                <td>FirstName</td>

                                                <td>LastName</td>

                                                <td>Username</td>

                                                <td>Phone #</td>

                                                <td>Location</td>

                                                <td>Title</td>

                                                <td>Role</td>

                                                <td class="table-action">

                                                <a href="#"><i class="align-middle" data-feather="edit-2"></i></a>

                                                <a href="#"><i class="align-middle" data-feather="trash"></i></a>

                                                </td>

                                            </tr>

                                        </tbody>

                                        <tfoot>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                            </tr>

                                        </tfoot>

                                    </table>

Hi,

I need to compare records in a table based on the 'datetime' field, like if the difference between time is less than 15min.

need to combine the records(rows) as a single resultant row until the time difference between them  is less than 15min and when a record's time difference is >15min that should return as another row and so on. Please find following sample table and the required output format

Sample Table:

Code: [Select]
ID NAME DATETIME
1   aaa    2011-10-10 06:30:00
2   bbb    2011-10-10 06:33:00
3   ccc    2011-10-10 06:38:00
4   ddd    2011-10-10 06:40:00
5   eee    2011-10-10 07:10:00
6   ffff    2011-10-10 07:14:00
7   sss    2011-10-10 08:16:00
8   jjj    2011-10-10 08:26:00
9   kkk    2011-10-10 08:28:00
10  mm    2011-10-10 09:46:00
11  ppp    2011-10-10 09:49:00
12  qqq    2011-10-10 09:52:00


Output Needed :

Code: [Select]
IDs START DATETIME END DATETIME
1,2,3,4 2011-10-10 06:30:00 2011-10-10 06:40:00
5,6 2011-10-10 07:10:00                      2011-10-10 07:14:00
7,8,9 2011-10-10 08:16:00 2011-10-10 08:28:00
10,11,12 2011-10-10 09:46:00                      2011-10-10 09:52:00

You can see 1st row in the output table having IDs 1,2,3,4 coz the time difference between them is less than 15minutes (it doesn't means time difference between start and end; it is actually the  time difference between current record and previous one ; ie; ID-1 and ID-2 have difference less than 15min and ID-2 & ID-3 have difference less than 15min and ID-3 & ID-4 have difference less than 15min, so those 4 records combine together as a single row also shows their start time and end time. Then you can see diff between ID-4 and ID-5 is greater than 15 min so it should display as new row, and so on)

How can I acheive above Output with mysql query ??

Please help..

Hi All, I want to copy into a table values from another table that partially match a given value, case-insensitively. So far I do as follows but I wonder whether there is a quicker way.

$input_table=array('1'=>'toto','2'=>'tota','3'=>'hello','4'=>'TOTO','5'=>'toto');
$input_table_2 = array_map('strtolower', $input_table);
$value_to_look_for='Tot';
$value_to_look_for_2=strtolower($value_to_look_for);
$output_table=array();
foreach ($input_table_2 as $k=>$v)
  {
  if(false !== strpos($v, $value_to_look_for_2))
    {
    $output_table[]=$input_table[$k];
    }
}

One drawback is that $input_table_2 is 'foreached' whereas there might be no occurrences, which would lead to a loss of time/resources for big arrays.   Thanks.

Hello,

I need some help. Say that I have a list in my MySQL database that contains elements "A", "S", "C", "D" etc... Now, I want to generate an html table where these elements should be distributed in a random and unique way while leaving some entries of the table empty, see the picture below. But, I have no clue how to do this... Any hints?



Thanks in advance,
Vero

Hi All ,   I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:   Day_ID -- Dues ---  Last_Visit ---  Points. 1  --------- 900 -------- 1/12  --------   6      2  --------- 700 -------- 4/12  --------   7 3  --------- 600 -------- 7/12  --------   5 4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.    So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:   4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >=
(
SELECT MAX(last_visit) FROM clubvisit
WHERE points <>
(
SELECT points as lastpoints
FROM clubvisit
JOIN
(
SELECT MAX(last_visit) as last_visit
FROM clubvisit
) as latest USING (last_visit)
)
)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.   As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible.    The values that I require for each of the cv.point columns is  1. day_id of the previous / old day on which the cv.points value changed from the current day value, and  2. cv.points on that old/ previous day.    So for  example if the table is as below:   Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2. 1  --------- 900 -------- 1/12  -----------   9  ------------ 5    2  --------- 600 -------- 4/12  -----------   6  ------------ 6 3  --------- 400 -------- 7/12  -----------   4  ------------ 7 4  --------- 500 -------- 9/12  -----------   5  ------------ 8 5  --------- 600 -------- 10/12 ----------   6  ------------ 8 6  --------- 600 -------- 11/12 ----------   6  ------------ 8 7  --------- 600 -------- 13/12 ----------   6  ------------ 7 8  --------- 500 -------- 15/12 ----------   5  ------------ 7 9  --------- 500 -------- 19/12 ----------   5  ------------ 7   Then I need the following set of values : 1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1 2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3) 3. day_id3 -- ....   and so on for other points.    Thanks all !

I have a table with a list of towns and cities with their postcode and latitude and longitude. Since a lot of small towns in an area have the same postcode.

I have a search which I want to return all towns that have the same postcode.

the table is set up as:

postcode:::::::town::::::::lat:::::::::long

I have a store number that i want to add and echo. This resembles an invoice so i my table has unit_price and total_value which is defined by by multiplying unit_price and pieces. Now that i want to print invoice i want to know how to add all items that are from that invoice_id

Code: [Select]
//get the total value for order
$sql_value = "SELECT total_value FROM `".TABLE_ORDERDETAILS."`WHERE order_id = $record_order[order_id]";
$rows_value = $db->fetch_all_array($sql_value);
foreach($rows_value as $record_value){
$value = $record_value[total_value];

}

Hi all,

I need to create a table which holds a list of values, for EG

1        PHP
2        MySQL
3        HTML
4        JS

The reason im doing this is so when a user visits X page on my site, they are presented with a list of possible options which they can tick, for instance if you were asked what languages your fluent with you would tick HTML and JS (or w/e applies to you)

What would be the best way of constructing the table?

Thanks

Hello Guys,

I am trying to insert a value in my table like this
image.jpg,image2.jpg,image3.jpg
Currently it just runs the image names together

Here is my code.. I tried to use explode, but it just adds the word "array" in the table.

Please advise


$filename2 = $phstring;
}

$csv = $filename2;

$fphoto = explode(',', $csv);

// Insert ad details into mysql
$query = "INSERT INTO ads (ad_type,ad_title,ad_body,ad_category,ad_photo,ad_member,ad_status,ad_city,ad_state,ad_zip)VALUES('".$ad_type."','".$ad_title."','".$ad_body."','".$catid."','".$fphoto."','".$vname."','".$ad_status."','".$city."','".$state."','".$zip."')";

I am under developing PHP add to cart without DB, so that i am using SESSION, actually get data from fetch_data.php data to my_cart.php using the POST method, successfully retun the values,

After receiving the post data how can i convert to display like a table.

workout:

fetch_data.php return values.

https://snag.gy/IASCMZ.jpg

& values received

https://snag.gy/ojWxHe.jpg

Here is my my_cart.php :

// FYI -> Here i am using only two fields :  voice_sku & voice_name 

<table width="100%" cellpadding="6" cellspacing="0">
 <thead>
 <tr>
 <th>Voice Sku</th>
 <th>Voice Name</th>
 <th>Remove</th>
 </tr>
 </thead>

 <tbody>

 <?php
 session_start();
 $voice_sku = '';
 $voice_name = '';

 if(isset($_POST['voice_sku'])&& isset($_POST['voice_name']))
            {
 // print_r($_POST);
 // die();
 
 $voice_sku = $_POST['voice_sku'];
 $voice_name = $_POST['voice_name'];
 $_SESSION['voice_sku'] = $voice_sku;
 $_SESSION['voice_name'] = $voice_name;
 
 
            }   
 var_dump($_SESSION);    
 ?>

 <tr>
 <td colspan="5">
 <span style="float:right;text-align: right;">
 <!--  -->
 </span>
 </td>
 </tr>
 <tr>
 <td colspan="5">
 <a href="index.php" class="button">Add More Items</a>
 <button type="submit">Update</button>
 </td>
 </tr>

 </tbody>
</table>

 

SELECT field, (array of values from table 2) FROM $table1 t1 LEFT JOIN $table2 ON t1.id=t2.id

... is something like this possible? (where I return records from t1 in a LEFT JOIN, but also get an array of any of the fields/records from the table 2)

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=330080.0

What gets put in for the values is my problem.  I have tried every way I can think of but I get errors in the code or I don't get the row inserted.

Can anyone give me a code sample using $ variables, preferably a multi-line list?  I would have to fix up my code to show you what I am trying as it is in between tries now.

I have to be doing something wrong and may have to put together a simple test script but a working example would be better.  Everything I look at on the web shows "xxxxx xxxx" examples not $variables.

Thanks in advance.

Hi there,

i'm trying to sum up values from mysql based on their date, specifically only values from entries with the same date should be added together, my db looks like this:

id, codes, value, date

now i'm building a statistics page, the value field is always 50, for showing the total for all the entries i'm just doing Code: [Select]
$totalincome = $codes * 50; and print that on the statistic, i want to have statistics for each day, the date is saved in this format: YYYY-MM-DD

how could i do that ?

Thanks !

Hi,