|
PHP - Getting All Values From A Table
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 Similar Tutorials
Table Issue - Multiple Location Values For User Pushes Values Out Of Row Instead Of Wrapping In Cell
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 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 ! Guys,
How can I compare two values in same table.
servis.kalkulacija_stavke ( id BIGINT(255) NOT NULL AUTO_INCREMENT, id_kalkulacija INT(255) NOT NULL, id_cjenika INT(255) NOT NULL, vrijeme TIMESTAMP DEFAULT CURRENT_TIMESTAMP, kataloski_broj VARCHAR(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, kategorija_artikla VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, grupa_proizvoda VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, podgrupa_proizvoda VARCHAR(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, cijena_EUR DECIMAL(20, 6) NOT NULL, cijena_KN DECIMAL(20, 6) NOT NULL, carina DECIMAL(20, 6) NOT NULL, spediter DECIMAL(20, 6) NOT NULL, banka DECIMAL(20, 6) NOT NULL, transport DECIMAL(20, 6) NOT NULL, nabavna_cijena DECIMAL(20, 6) NOT NULL, drezga_marza_po_grupi DECIMAL(20, 6) NOT NULL, drezga_zarada DECIMAL(20, 6) NOT NULL, neto_VPC DECIMAL(20, 6) NOT NULL, neto_MPC DECIMAL(20, 6) NOT NULL, trosak_firme DECIMAL(20, 6) NOT NULL, trosak_firme_p DECIMAL(20, 6) NOT NULL, diler_marza_po_grupi DECIMAL(20, 6) NOT NULL, preporucena_VPC DECIMAL(20, 6) NOT NULL, preporucena_MPC DECIMAL(20, 6) NOT NULL, zarada_diler_kn DECIMAL(20, 6) NOT NULL, zarada_diler_post DECIMAL(20, 6) NOT NULL, zarada_za_nas_kn DECIMAL(20, 6) NOT NULL, zarada_za_nas_post DECIMAL(20, 6) NOT NULL, brutto_zarada_za_nas_kn DECIMAL(20, 6) NOT NULL, brutto_zarada_za_nas_post DECIMAL(20, 6) NOT NULL, godina YEAR(4) NOT NULL, PRIMARY KEY (id), INDEX id (id), INDEX kat_br (kataloski_broj), INDEX kataloski_broj (kataloski_broj, kategorija_artikla, grupa_proizvoda, podgrupa_proizvoda) ) ENGINE = MYISAM AUTO_INCREMENT = 1119990 AVG_ROW_LENGTH = 264 CHARACTER SET utf8 COLLATE utf8_general_ci;I want to compare field nabavna_cijena in last year and this year. How can I achieve that? Edited by budimir, 15 December 2014 - 08:12 AM. 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 I'm not completely sure how to implement this so I'd like to ask you to give me an idea if you can For a tennis booking system.. it will look something similar to (should be a table but this is for the sake of the idea) Date: Oct 25th, 2010 (go to oct 26) Court 1 Court 2 Court 3 Court 4 8:00 Open(bookit) Tally Open Hellio 9:00 Andy Roger Barney Training ...etc Now everyday, this table should be empty, so new bookings can be made how would I make it so a new table is empty /or has dif bookings every day (based on date) efficiently? Im guessing I will have dif values in the sql table for dif dates but what I'm not sure of is how to display it in page for ex, if someone presses the button oct 26, the new table is for that date, wif diff values 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) 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>
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. 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."')"; This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330080.0 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 ! I want to take data from one table and insert it into another in the same database, the problem is the two tables have different values so it wouldn't be as simple as using an INSERT INTO script. From table 1 I want to extract a row ID as well as a field called systems. I then want to insert that into the second table which is structured, Id (null) Name (games) Value (the data given from Systems in the other) ID2 (the ID from the other table) Category (news) Any ideas how I can go about this? Apologies... I'm a noob trying to reverse engineer code. I have a script that can retrieve values from a MySQL database (based on a session login that recognizes which user you are) and places those values into the text fields of a form, pre-populating the form. Code: [Select] <?php mysql_connect('localhost', 'db', 'password'); mysql_select_db('users'); $id = mysql_real_escape_string($_SESSION['userid']); $select = "SELECT * FROM user_info WHERE md5(Member_No) = '$id';"; $query = mysql_query($select); if ($row = mysql_fetch_array($query)) { ?> <form> FIRST name: <input type="text" name="First" value="<?php echo stripslashes($row['First']); ?>" LAST name: <input type="text" name="Last" value="<?php echo stripslashes($row['Last']); ?>" > Birthday: <input type="text" name="Birthday" value="<?php echo stripslashes($row['Birthday']); ?>" <input type="submit" value="Submit"> </form> <?php } ?> I want to be able to connect to that same database and update ALL the values (3 in the example above) with the single click of the form button. I think I was told some time ago that it's not possible to update all the values at once? Thanks, ~Wayne I'm trying to add ui autocomplete functionality to some fields in a form and am running into a few issues. The first is I want to be able to both add and pull multiple values from the one field. I had originally thought I could comma delimit the values and handle it that way, but it seems it's a bit more complicated than that. I've tried to explode the value but it doesn't go far enough, I think I need to find a way to refine the array down by trying to replace the comma in the code somehow. Here's my current php code: <?php //connection information $host = "localhost"; $user = "myuser"; $password = "mypass"; $database = "mydb"; $param = $_GET["term"]; //make connection $server = mysql_connect($host, $user, $password); $connection = mysql_select_db($database, $server); //query the database $query = mysql_query("SELECT cb_activities FROM jos_comprofiler WHERE cb_activities REGEXP '^$param'"); //build array of results for ($x = 0, $numrows = mysql_num_rows($query); $x < $numrows; $x++) { $row = mysql_fetch_assoc($query); $activities[$x] = array(cb_activitiesterm => $row[cb_activities]); } //echo JSON to page $response = $_GET["callback"] . "(" . json_encode($activities) . ")"; echo $response; mysql_close($server); ?> Someone else had mentioned maybe using something like this: $string="([{\"cb_activitiesterm\":\"Kicking Cats, Working\"},{\"cb_activitiesterm\":\"Web Development\"}])"; $one=str_replace("([{\"cb_activitiesterm\":\"","",$string); $two=str_replace("\"},{\"cb_activitiesterm\":\"",",",$one); $three=str_replace("\"}])","",$two); echo $three; // now you can explode it ... by commas $items=explode(",",$three); But of course the problem here is he's creating the string in the php file and adding the /'s when in reality this needs to be pulled from the database and then refined. The values are currently formatted like this: ([{"cb_activitiesterm":"Kicking Cats, Working"},{"cb_activitiesterm":"Web Development"}]) Exploding simply adds a "" around the comma so it won't work. I need it to format like this: ([{"cb_activitiesterm":"Kicking Cats"},{"cb_activitiesterm":"Working"},{"cb_activitiesterm":"Web Development"}]) My second issue is I need to be able to pass multiple values into the hidden input in the script and adding a comma/space between each value. Currently if you click another value it simply replaces the prior value with the next one. Here's my script: Code: [Select] <div id="editcbprofile"> <div id="formWrap"> <div id="activities" class="ui-helper-clearfix"> <input id="cb_activitiesterm" type="text"> <input id="cb_activities" name="cb_activities" type="hidden"> </div> </div></div> <script type="text/javascript" src="autocomplete/js/jquery-1.4.2.min.js"></script> <script type="text/javascript" src="autocomplete/js/jquery-ui-1.8.custom.min.js"></script> <script type="text/javascript"> $(function(){ //attach autocomplete $("#cb_activitiesterm").autocomplete({ //define callback to format results source: function(req, add){ //pass request to server $.getJSON("autocomplete/autocom.php?callback=?", req, function(data) { //create array for response objects var suggestions = []; //process response $.each(data, function(i, val){ suggestions.push(val.cb_activitiesterm); }); //pass array to callback add(suggestions); }); }, //define select handler select: function(event, ui) { //create formatted activity var activity = ui.item.value, span = $("<span>").text(activity), a = $("<a>").addClass("remove").attr({ href: "javascript:", title: "Remove " + activity }).text("x").appendTo(span); //add interest to activities div span.insertBefore("#cb_activitiesterm"); //add activity to hidden form field $('input[name=cb_activities]').val(activity); }, //define select handler change: function() { //prevent 'to' field being updated and correct position $("#cb_activitiesterm").val("").css("top", 2); } }); //add click handler to activities div $("#activities").click(function(){ //focus 'to' field $("#cb_activitiesterm").focus(); }); //add live handler for clicks on remove links $(".remove", document.getElementById("activity")).live("click", function(){ //remove current activity $(this).parent().remove(); //add activity to hidden form field $('[name=cb_activities]').val().remove(); //correct 'to' field position if($("#activities span").length === 0) { $("#cb_activitiesterm").css("top", 0); } }); }); </script> And then my 3rd issue is if you remove a value it needs to also remove it from the hidden input. I've tried several attempts of the remove() in the onclick function but I can't get it to remove it. Lol other than that it's cherry. Any advice on any of these would be great. Hey guys, I need your help on this please. I am working on a php web application. I have a form that gets information for Supplier and his traveling details. The values are stored in arrays. I would like to store supplier details in Supplier table and the travelling details in the Travel table. Here is the code with the comments. <?php if (isset($_VARS['submitSuppliers'])) { //error checking starts here $isError = false; if (empty($_VARS['supplierName'])) { $isError = true; $_VARS['supplierRequest']['supplierName']['errorMessage'] = 'You need to provide a Supplier Name.'; } if (empty($_VARS['address'])) { $isError = true; $_VARS['supplierRequest']['address']['errorMessage'] = 'You need to provide an Address.'; } if (empty($_VARS['travel'])) { $isError = true; $_VARS['supplierRequest']['city']['errorMessage'] = 'You need to provide travelling details.'; } #this checks if there is no error and then proceeds to save form values if (!$isError) { #This function adds the details and Returns the autoincremented Supplier ID $SupId = add_suppliers($_VARS); if ($SupId) { #=== Now here I would like to save the travelling details in the Travel table using the generated supplier id # $travId = add_Travel($_VARS); //==== how do I segregate only travel details from the $_VARS array to store in travel table? REDIRECT('/supplier/index.html', array('confirmationMessage' => 'Your details have been saved.')); }else { notify_admin('supplierjoin.php'); } }else { $_VARS['supplierRequest']['errorMessage'] = "There was a problem with the information you provided. Please check the form and try again."; } } ?> Kindly help. Any comment or feedback is always welcome. Thank you I am in need of suggestions on how too achive this: I have a surf exchange and whenever a member has viewv a site, it ads this members Id and time to a table called vtp_tracking. I have now created a team script, so I can count the total of surf for members within this team from the vtp_table. What I am in need of is this: I would like to add a bonus for the teams, so my idea was this: every hour run a cronjob to get the results for the last hour by team id lik so: Code: [Select] WHERE vtp_members.team_id="teamid" AND vtp_tracking.credit_members_id=vtp_members.id AND vtp_tracking.action_date>date_sub(NOW(),interval 60 minute I believe the above code will work fine and get me the results for team members for the last hour. Problem: how can I get this result, multiple it with the value from another field and divide by 100 and add this to a third field? eg: (result from above * value from other field / 100) then add to a third field. Anyone willing to try and answer this one? |
|||||||