Jump to content

Search the Community

Showing results for tags 'sql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (Dreamweaver, Zend, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start





Website URL








Donation Link

  1. Hi everyone, I am trying to make it simply, but seem error come repeatedly, I could not figure why it went wrong. I am trying to get print (echo) to visible the print before I build other script. but resilt said "Notice: Array to strong conversion in" what did I do wrong? Thanks, Gary <!doctype html><html><body><table><?php require("require2.php"); $show = "show tables from XXXX";$table = mysqli_query($GaryDB, $show);$array = array();while($array = mysqli_fetch_array($table)) { echo $array;}?></table></body></html>
  2. Hi, I am just getting a blank page and I am unable to figure out why such a simple concept is not working. Any help would be greatly appreciated. <?php //Error Check ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); try { $conn = new PDO("mysqli:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $statement = $conn->prepare("UPDATE test SET title= ? WHERE id= ?"); $statement->bind_param('si', $title,$id); $statement->execute(); if ($statement->affected_rows >0) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn->error; } $statement->close(); ?>
  3. Hi all, I currently have a table listed, with one of the rows being hyperlinked and linking to an input form, should be autopopulated from the code (ID or other unique identifier) of the row, however, when clicking the link the form does not load. I will insert relevant code to this thread and if anyone would be able to shed some light on this that would be great. NOTE: SQL code has already been tested using PHPMyAdmin and works fine. Although, it may need changed to help with this issue - I'm not quite sure. Please see screenshots for relevant code.
  4. i have this table +------+---------------------------------------------------------+---------------------+-------------------+--------+-------+ | P_id |shortname | group_concat(Apps1) | group_concat(Gls) | t_Apps | t_Gls | +------+---------------------------------------------------------+---------------------+-------------------+--------+-------+ | 1 | Friendly,NDIV V | 1,1 | 3,0 | 2 | 3 | | 2 | Friendly,NDIV V | 1,1 | 1,0 | 2 | 1 | | 3 | Friendly,NDIV V | 1,1 | 0,1 | 2 | 1 | | 4 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 10 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 11 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 12 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 13 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | +------+---------------------------------------------------------+---------------------+-------------------+--------+-------+ 8 rows in set, 1 warning (0.29 sec) how can i populate its data into an html table with this structure using php <table> <thead> <tr> <th class=" header" colspan="1">2015/2016</th> <th class=" header" colspan="2">NDIV V Lge</th> <th class=" header" colspan="2">Friendlies</th> <th class=" header" colspan="2">Total</th> </tr> <tr> <th class="header">P_id.</th> <th class=" header">Ap</th> <th class=" header">Gl</th> <th class=" header">Ap</th> <th class=" header">Gl</th> <th class="header">Ap</th> <th class="header">Gl</th> </tr> </thead> <tbody> <tr> <td>43</td> <td class=" ">0 + 0</td> <td class=" ">0</td> <td class=" ">0 + 0</td> <td class=" ">0</td> <td>0 + 1</td> <td>0</td> </tr><tr> <td>42</td> <td class=" ">0 + 0</td> <td class=" ">0</td> <td class=" ">0 + 0</td> <td class=" ">0</td> <td>0 + 2</td> <td>0</td> </tr><tr> <td>36</td> <td class=" ">1 + 0</td> <td class=" ">0</td> <td class=" ">0 + 0</td> <td class=" ">0</td> <td>7 + 10</td> <td>2</td> </tr></tbody> </table>
  5. I have this table (tblStats): +------+-----------+------+------------+----------+-------+------+ | P_id | ShortName | logo | SeasonName | SeasonID | Apps1 | Gls | +------+-----------+------+------------+----------+-------+------+ | 1 | Friendly | NULL | 2016/17 | 12 | 1 | 3 | | 1 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 2 | Friendly | NULL | 2016/17 | 12 | 1 | 1 | | 2 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 3 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 3 | NDIV V | NULL | 2016/17 | 12 | 1 | 1 | | 4 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 4 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 10 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 10 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 11 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 11 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 12 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 12 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 13 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 13 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | +------+-----------+------+------------+----------+-------+------+ 16 rows in set, 1 warning (0.07 sec) I have this query ti get records for a particular P_id from the table above: select P_id,group_concat(ShortName),group_concat(Apps1),group_concat(Gls),SUM(Apps1) as t_Apps,sum(Gls)as t_Gls from (SELECT houseid as P_id, ShortName, logo, SeasonName, SUM(Apps) as Apps1, SUM(Gl) as Gls FROM tblStats where SeasonName='2016/17' GROUP BY P_id ; which outputs: +------+-------------------------+---------------------+-------------------+--------+-------+ | P_id | group_concat(ShortName) | group_concat(Apps1) | group_concat(Gls) | t_Apps | t_Gls | +------+-------------------------+---------------------+-------------------+--------+-------+ | 1 | Friendly,NDIV V | 1,1 | 3,0 | 2 | 3 | | 2 | Friendly,NDIV V | 1,1 | 1,0 | 2 | 1 | | 3 | NDIV V,Friendly | 1,1 | 1,0 | 2 | 1 | | 4 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 10 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 11 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 12 | NDIV V,Friendly | 1,1 | 0,0 | 2 | 0 | | 13 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | +------+-------------------------+---------------------+-------------------+--------+-------+ But the shorname is not in order as in Friendly,NDIV V.But there is an interchange as we go on.This will affect the data displayed how can I clear this?
  6. I've come up with an SQL sum query: $query = "SELECT SUM(calories) FROM tracklog AS calories WHERE userid = $_SESSION[userid]"; No errors are shown, however how do I get this value to be displayed using HTML? I want it to be displayed after 'Total number of calories lost to date:'
  7. ihave this table structure CREATE TABLE staff_details_tbl ( staff_id INT UNSIGNED NOT NULL AUTO_INCREMENT, SUsername_id varchar(255), Fname VARCHAR(60) NOT NULL, Lname VARCHAR(60) NOT NULL, D_O_B date, PRIMARY KEY (staff_id ) ); Fname and Lname for more than 1 human being can be the same i have this stored procedure code: declare l_staff_id int; insert into staff_details_tbl (Fname,Lname,D_O_BSUsername_id)values(inFname,inLname,inBdate,' '); select staff_id into l_staff_id from staff_details_tbl where Fname=inFname and Lname=inLname ; insert into staff_job_tbl(staff_id,Team_catId,job_id)values(l_staff_id,inteam,injob); ihave to select the staffid for insert into another table, there are some problems with using the last_insert_id() that is why i choose to select the staff_id here. but with no unique Fname or Lname,the repetition of same names causes an error of REsult contained more than one row no unique key because there re many people with a name like 'kamikazi' google and see. I need a way of getting that id how can I go about this??
  8. i have this table A +---------+---------------+----------+ | MatchID | CompetitionID | SeasonID | +---------+---------------+----------+ | 27 | 8 | 12 | | 28 | 10 | 12 | +---------+---------------+----------+ 2 rows in set (0.00 sec) and this table B +------+-------------------------------+----------+---------------+------------------------+ | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +------+-------------------------------+----------+---------------+------------------------+ | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 2016 | UN Peace Cup Winners | 12 | 9 | UN Peace Cup | | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 2016 | NDIV V Winners | 12 | 7 | Nakawa Fifth division | | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 2015 | UN Peace Cup Winners | 13 | 9 | UN Peace Cup | | 2015 | Kyanja U-14 Cup Winners | 13 | 10 | KYANJA U-14 Cup | | 2015 | NDIV V Winners | 13 | 7 | Nakawa Fifth division | +------+-------------------------------+----------+---------------+------------------------+ 8 rows in set (0.02 sec) how can i select a competitionId from table B corresponding to a group of matches with same competitionID and seasonID in table A
  9. I would like to have this +3 apply when the paymentterm if is less than 45. So the current code is using case statement. SELECT SUM(sblPOAmount) AS totalPOAmt, case when paymentTerm = '45 Days' then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 0, SBLInvoiceDate)) end FROM [A_Sys].[dbo].[Eventtbl] WHERE DATENAME(MONTH, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='June' AND DATENAME(YEAR, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='2017' Group By paymentTerm,SBLInvoiceDate The Sum having multiple result due to "Group By", but if i run it without Group By , it will have error message. Please help
  10. I have a login session where it checks the user name and displays it to the form (it displays FirstName LastName). That username is also being used as a variable to pull up data in db. Now I also have another page where user's can update the db, I dont have a problem if the user will update it with a complete FirstName LastName entry because it will just be the same as the one's being used by the login session, but sometimes they just update it with FirstName. The problem starts when I have values in TEST db under Tester column and in USERS db under User column that is of different values. It would have two diff values when a user did not enter the full FirstName LastName. For example, FirstName1 LastName1 is the value in USERS db User column - this is fixed and is being used in a login session User updated Tester column in TEST db with just FirstName1 - this is different from the User column above Here is what I am trying to do, Getting list of tickets from the TEST db where datefrom and dateto and using a variable for the values that is in User column under USER DB If User column under USERS db = Tester column under Test db which is FirstName1 LastName1 - it will be good as I will be able to get tickets under FirstName1 LastName1. But I will not be able to get ticket which is still assigned to that same person because the value in Tester column under Test db is just FirstName1. If User column under USERS db (FirstName1 LastName1) is not equal to Tester column under Test db which is just FirstName1 - I will not get tickets assigned to FirstName1 as my variable is equal to FirstName1 LastName1. I hope that I explained it clearly, here is my code, Here is the variable that I am posting, $uid = false; if(isset($_POST['uid'])){ $uid = $_POST['uid']; } And here is the query, $sql = 'SELECT `id`, `date_implemented`, `tester`, `comments` FROM `tracker` WHERE `tester` = :uid AND `scheduled_start_date` BETWEEN :d1 AND :d2'; $stmt = $conn->prepare($sql); $stmt->bindParam(':uid', $uid, PDO::PARAM_STR); $stmt->bindParam(':d1', $date['from'], PDO::PARAM_STR); $stmt->bindParam(':d2', $date['to'], PDO::PARAM_STR); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); I would need help in passing that variable (uid) such that I can use `WHERE tester LIKE uid`. In that case whether the tester column just contains FIRSTNAME1 or a complete FIRSTNAME1 LASTNAME1, I would be able to get all tickets assigned to FIRSTNAME1.
  11. This seems like something I've done several times a year for years, but I can't seem to get it. I can't find a solution on Google that works, although the question was asked many times and answered, too. Also, I'm low on sleep right now. Let's say I have two tables: USERS USERS_LOGINS I want to join the tables and get one row per user, with only the latest login row. Assume "UserId" is the pk/fk and there's some sort of LoginTime in the USERS_LOGINS table. So I will get results like: - Brian 8/4 - Mitch 8/3 - Jerry 8/2 I appreciate the help.
  12. Insert into test.ambition_test(Extension, ExtID, Total_Talk_Time_seconds, Total_Talk_Time_minutes,Total_Outbound, Total_Inbound, Missed_Calls, Total_Calls, Date_of_report, Time_of_report) SELECT c.extension as Extension ,RESPONSIBLEUSEREXTENSIONID as ExtID , sum(Duration) as Total_Talk_Time_seconds , round(sum(Duration) / 60,2) as Total_Talk_Time_minutes , sum(if(LEGTYPE1 = 1,1,0)) as Total_Outbound , sum(if(LEGTYPE1 = 2,1,0)) as Total_Inboundambition_test , sum(if(Answered = 1,0,1)) as Missed_Calls , count(DISTINCT b.NOTABLECALLID) as Total_Calls , DATE(NOW()) , NOW() FROM cdrdb.session a LEFT JOIN cdrdb.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID LEFT join cdrdb.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID WHERE b.ts >= curdate() AND c.extension IN (7295,7306,7218,7247,7330,7000,7358) group by c.extension ON duplicate key update Total_Talk_Time_seconds =values(Total_Talk_Time_seconds), Total_Talk_Time_minutes =values(Total_Talk_Time_minutes), Total_Outbound = values(Total_Outbound), Total_Inbound = values(Total_Inbound), Missed_calls = values(Missed_Calls), Total_Calls = values(Total_Calls), Date_of_report = values(Date_of_report), Time_of_report = values(Time_of_report); The above query is what I have running in MySQL workbench currently. It's pulling and joining several tables to get call data and form metrics for 7 CSR agents on our phone system. IT pulls the data and forms the metrics correctly and it also updates throughout the day properly, but I have a huge problem: It will not insert new records on each new day, it still overwrites per day whereas I want to keep a history. I created a unique index on the Primary key Extension as well as the Date_of_report column as well. To specify, this query runs every 15 minutes to add/aggregate the metric totals based on the extension, however, the next day we should be inserting 7 new records and then have those aggregate through the day. Basically by the end of 3 days, we would have 21 records. However, this just continues to overwrite every day. Any ideas or tips here?
  13. I am trying to retrieved data from database and update a single column. Here is a sample of how my page would look like, first part is when we initially upload it, this is during the approval process column1 | column2 | value1 | value1 | value2 | value2 | Now the second part is when we try to retrieved the data from the db with the added column3 because we want to assign it to someone, thus adding their name before we re-upload it to the db column1 | column2 | column3 value1 | value1 | ------- value2 | value2 | ------- <=== this column3 rows does not have value in database yet as it is not part of the ... page I used when we initially uploaded the data, so it will be blank when I retrieved the data I am trying to retrieved multiple rows here with column3 being editable so we can add different values to it. Then I want to re-upload those different values in column3 alone. Here is how I'm fetching the data, <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "sample_db"; // check data before use it and convert from string to expected type, use try, not like here: $date = $_POST['date']; $date1 = $_POST['date1']; // use valid data to select rows try { //1. connect to MySQL database $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); //2. set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //3. create query string (here is answer on your question) $sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2'; //4. prepare statement from query string $stmt = $conn->prepare($sql); //5. bind optional parameters //if ($status != 'All') $stmt->bindParam(':st', $status); //6. bind parameters $stmt->bindParam(':d1', $date); $stmt->bindParam(':d2', $date1); //7. execute statement $stmt->execute(); //8. returns an array containing all of the result set rows $result = $stmt->fetchAll(PDO::FETCH_ASSOC); //get count of rows $numrow = count($result); //print array - there is many solution to print array, //to debug you can do: //print_r($result); } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; if($numrow == 0) echo "No results found."; else echo "Count: $numrow</br>"; { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>--> <th align='center'><strong>Column1</strong></th> <th align='center'><strong>Column2</strong></th> <th align='center'><strong>Column3</strong></th> </tr>"; foreach ($result as $row => $info) { echo "<form action='crqretrieve_status.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>"; echo "<td align='center'>" . "<input name=column3 value='' </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; ?> Now, I have a code here which I am using to update status but it is only applicable for single row update which is based on the "ID". How can i use this for multiple row update? I've read about using a "case" but i dont know how to implement it using the code below. Here is the code to retrieved data from db and update row but only for single ID, i cant make it to work when retrieving multiple ID's and updating multiple ID's after clicking a Submit button. <?php /* Allows the user to both create new records and edit existing records */ // connect to the database include('include/connect-db.php'); // creates the new/edit record form // since this form is used multiple times in this file, I have made it a function that is easily reusable function renderForm($column1 = '', $column2 = '', $column3 = '', $column4 = '', $error = '', $id = '') { ?> <html xmlns="http://www.w3.org/1999/xhtml"> <body class="oneColFixCtrHdr"> <div id="container"> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <div id="mainContent"> <form action="" method="post"> <table width="450" border="0" align="center"> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID: <?php echo $id; ?></p> <?php } ?> <tr> <td>A1:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column1" value="<?php echo $column1;?>"/></td> </tr> <tr> <td>A2:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column2" value="<?php echo $column2; ?>" readonly="readonly"/></td> </tr> <tr colspan="3"> <td>A3:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column3" id="column3" value="<?php echo $column3; ?>"/></td> </tr> <tr> <td><input type="submit" name="submit" value="Submit" /></td> </tr> </table> </form> <!-- end #mainContent --></div> <!-- end #container --></div> </body> </html> <?php } /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $column1 = $_POST['column1']; $column2 = htmlentities($_POST['column2'], ENT_QUOTES); $column3 = htmlentities($_POST['column3'], ENT_QUOTES); // check that fields are not empty if ($column1 == '' || $column2 == '' || $column3 == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($column1, $column2, $column3, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, WHERE id=?")) { $stmt->bind_param("sssi", $column1, $column2, $column3, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: list.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT column1, column2, column3 FROM sample_table WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($column1, $column2, $column3); $stmt->fetch(); // show the form renderForm($column1, $column2, $column3, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: list.php"); } } } // close the mysqli connection $mysqli->close(); ?>
  14. I have a page of html tables that are filled by database values through PHP, and there are no problems there. However, I have an issue on one of the tables. I have a pricing table, attached below. The $575 is currently filled from a field in the database that is set from a CSV upload, but the new CSV template is not doing this because it's missing the price field. This is the SQL statement that I'm using in the database, since the price is based off of meterSize, meterType and workOrderType2: UPDATE staging INNER JOIN pricing ON staging.meterType = pricing.meterType AND staging.meterSize = pricing.meterSize AND staging.workOrderType2 = pricing.workOrderType SET staging.onsiteTestSurveyPrice = pricing.price This works, but only with the original template, so I'm looking into doing this strictly in PHP with the table/row values, if possible. An example of what I'm looking for, using the html table and PHP: If $row['meterSize'] contains 3", $row['meterType'] contains COMPOUND and $row['workOrderType2'] contains ONSITE SURVEY AND TEST then $575 should go in <td><? echo $row['onsiteSurveyTestCost'];?> </td> Here's the code for those 3 conditional table rows: <tr style="border: none;"> <td style="border: none; text-align: left;">Meter Type:</td> <td style="border: none; text-align: right;"><? echo $row['meterType'];?> </td> </tr> <tr style="border: none;"> <td style="border: none; text-align: left;">Meter Size:</td> <td style="border: none; text-align: right;"><? echo $row['meterSize'];?> </td> </tr> <tr style="border: none;"> <td style="border: none; text-align: left;">Service Preformed:</td> <td style="border: none; text-align: right;"><? echo $row['workOrderType2'];?> </td> </tr> Is there a way to do this? I have multiple prices that are based on multiple conditions, but If I could figure out how to do this once then I can alter the values accordingly. Basically I want to mirror the db/SQL statement only with strictly PHP and the values in each table row. How can I do that, if possible?
  15. I have a search function that works perfectly by matching database values and displaying the records on my page. However, I want to make my serial number field in the output a "href " link to open a new page that uses the serial number in the code. I had it working previously with dropdowns but I'm trying to make it work with the search function. Here's the working code: $search = $connect->real_escape_string($_POST['search']); $resultSet = $connect->query("SELECT * FROM staging WHERE serialNumber LIKE '%$search%'"); if($resultSet->num_rows > 0){ while($rows = $resultSet->fetch_assoc()) { $date = $rows['date']; $utility = $rows['utility']; $address = $rows['address']; $sn = $rows['serialNumber']; $output .= "Date: $date <br />Utility: $utility<br />Address: $address<br />Serial Number: $sn<br /><br />"; } }else{ $output = "No Results"; } } Now, when I try to add the link to the serial number variable, like this: $output .= "Date: $date <br />Utility: $utility<br />Address: $address<br />Serial Number: <a href=\\'/dashboard-display?id='.$row['serialNumber'].''>$sn</a><br /><br />"; The page doesn't load. I'm using a PHP reference but I think I may have syntax wrong in the href section. How can I get this to display the info with the link tag around $sn?
  16. I have an html table with some numerical values in each data row. There are 8 columns representing 8 tests. At the bottom of each column is a corrected accuracy cell which represents a math formula from the three rows above it. Basically, it divides the meter volume by the tester volume, multiplies that result by the tester accuracy, and divides that result by 100. I have attempted something with the following code: <table style="width:100%; border:none; border-collapse:collapse;"> <? php $test1FormA = $row['test1MeterVol'] / $row['test1TesterVol']; $test1FormB = $test1FormA * $row['test1Accuracy']; $test1FinalForm = $test1FormB / 100; ?> <tr> <td style="border:none; text-align: left;">Meter Volume: </td> <td><? echo $row['test1MeterVol'];?> </td> </tr> <tr> <td style="border:none; text-align: left;">Tester Volume: </td> <td><? echo $row['test1TesterVol'];?> </td> </tr> <tr> <td style="border:none; text-align: left;">Tester Accuracy: </td> <td><? echo $row['test1Accuracy'];?> </td> </tr> <tr> <td style="border:none; text-align: left;">Corrected Accuracy: </td> <td><? echo $test1FinalForm;?> </td> </tr> </table> However, my page no longer loads with this so I'm assuming I'm doing something wrong with my variables in the top, maybe with syntax. I just need it to take the values for those three data rows and use them in the formulas so that I can put the final result in the corrected accuracy field.
  17. I have a page that displays about 12 tables that are filled from database values. These values correspond with a single record but some of these fields are based off of queries I'm making so that if something's wrong in the query, it will display red text. I'm only doing this for a few different fields but I want an option to edit and fix those fields and then save them to the database with an UPDATE. Basically, I don't want to wrap the whole page in a form since only a few fields in a few of the tables will need to be editable. Is there a way to do this so that I can edit only the few fields that need to be fixed? Here's a table as example: <div class="testResults" style=" width: 30%; clear:both; float: left;"> <!--Test Results Table--> <table style="width: 100%;"> <tr> <th colspan="2">Test Results</th> </tr> <tr> <td style="text-align: left;">Tested Within AWWA Standards</td> <td><? echo $row['standards'];?> </td> </tr> <tr> <td style="text-align: left;">Meter Failed on Low Side</td> <td><? echo $row['lowFail'];?> </td> </tr> <tr> <td style="text-align: left;">Meter Failed on High Side</td> <td><? echo $row['highFail'];?> </td> </tr> <tr> <td style="text-align: left;">Meter Failed Low and High Side</td> <td><? echo $row['bothFail'];?> </td> </tr> <tr> <td style="text-align: left;">Unable To Test</td> <td><? echo $row['unableTest'];?> </td> </tr> </table> So, for example, say I want to edit the data for just standards row and lowFail row. I tried this but it didn't work: <td><input type="text" name="test8TRGPM" value="<? echo $row['test8TestRateGPM'];?>"> </td> I know once this is done I'll need to have a submit button that attaches to a query to update with the input name, but right NOw i'm trying to find the best way to edit. Is it easiest to just wrap the whole page in a form and just create the few inputs where I need?
  18. I have a form that is populated by a CSV file, always 229 fields but anywhere up to 10 or 12 rows. The form populates correctly, but I'm having issues saving it all. The loop seems like it's missing something or maybe something is mismatched. Here is the form code: **page-confirm.php** $connect = mysqli_connect($server, $user, $pw, $db); if ($connect->connect_error) { die("Connection failed: " . $conn->connect_error); }else{ //echo'success!'; } if(isset($_POST['preview'])) { ini_set('auto_detect_line_endings', true); $file = $_FILES["file"]["tmp_name"]; $handle = fopen($file, "r"); $maxPreviewRows = PHP_INT_MAX; // this will be ~2 billion on 32-bit system, or ~9 quintillion on 64-bit system $hasHeaderRow = true; echo "<form method='post' action='/form-submit' >"; echo '<table>'; if ($hasHeaderRow) { $headerRow = fgetcsv($handle); echo '<thead><tr>'; foreach($headerRow as $value) { echo "<th>$value</th>"; } echo '</tr></thead>'; } echo '<tbody>'; $rowCount = 0; while ($row = fgetcsv($handle)) { $colCount = 0; echo '<tr>'; foreach($row as $value) { echo "<td><input name='row[".$rowCount."][".$colCount."]' type='text' value='$value' /></td>"; $colCount++; } echo '</tr>'; if (++$rowCount > $maxPreviewRows) { break; } } echo '</tbody></table>'; echo "<input type='submit' name='confirm' value='confirm'>"; echo '</form>'; } ?> Here's the code for the submission page with the insert loop: **page-formsubmit.php** $connect = mysqli_connect($server, $user, $pw, $db); if ($connect->connect_error) { die("Connection failed: " . $connect->connect_error); }else{ echo'success!'; } //var_dump($_POST); $sql = $inserts = $binds = []; foreach ($_POST['row'] as $rowValue){ if(is_array($rowValue) && count($rowValue) > 0 ){ foreach($rowValue as $rowData){ /*** * Stupidly, I had missed that row contains arrays * rather than values, so you need a foreach, inside the * foreach as so: ***/ foreach ($rowData as $columnKey => $columnValue){ //$columnValue will now equal $value //$columnKey will be the column number (1...229) /*** * This is the area you can construct your SQL query values. * db_connection is assumed to be setup. ***/ $sql[] = "`column_name_".$columnKey."`"; $binder = "value".$columnKey; $inserts[] = ":".$binder; $binds[$binder] = $columnValue; unset($binder); } unset($columnKey,$columnValue); } unset($rowData); /*** * This is the area the SQL query is set on a per row basis ***/ $sqlFull = "INSERT INTO staging (".implode(",",$sql).") VALUES(".implode(",",$inserts).")"; $connect->prepare($sqlFull); /*** * EDIT: bind param MUST come after the prepare call ***/ foreach($binds as $bindKey=>$bindRow){ $connect->bind_param(":".$bindKey, $bindRow); } unset($bindKey,$bindRow); var_dump($binds); $sql = $inserts = $binds = []; //reset arrays for next row iteration. /*** * db_connection then executes the statement constructed above ***/ $connect->execute(); } //close if. } unset($rowValue); ?> You can see in the submission page where I comment out the var_dump for post data. When it's active, it prints out this for the array: success!array(1) { ["row"]=> array(5) { [0]=> array(229) and continues printing the 229 elements for all 7 rows. There is something that's not quite working in this loop because even a debug statement within the loop to print the query won't work. I just need to make sure it iterates all 229 fields of every row to the end of the form and puts them into my staging table. My staging table, which is temporary, has 229 named fields in the exact order as the CSV, so I shouldn't have to declare each field I don't believe.
  19. Could someone tell me what is the query for what im trying to do, for example: i have 2 tables, one is "comments" table and the other is "articles" tables. In the comments table there is a 3 columns (id, title, message) and in the articles table there is 3 columns too(id, title,info) I would like is to get all the data of the column message from the table "comments" but the table "comments" got to have the same title of the column title of the column title of Articles table. I dont no if im beign confusing, im sorry.
  20. Hi I'm trying to run a cron here to remove profile pictures delete users delete private messages delete messages but I need some help to make my code more clean and effective <?php include("chat_code_header.php"); //// Delete profile pictures and users id older than 30 days//// $querybd = "SELECT Username, last_online, pprofilepic FROM Users2 WHERE last_online < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))"; $resultbd = mysql_query($querybd) or die(mysql_error()); $users = array(); while($rowbd = mysql_fetch_array($resultbd)){ if ($rowbd['Username'] != '') $users[] = $rowbd['Username']; if($rowbd['pprofilepic'] == ""){}else{ $pprofilepic = realpath($rowbd['pprofilepic']); print "\n Pictures Removed:\n" . $pprofilepic . "\n"; unlink($pprofilepic); } } $list = implode('; ', $users); if($list == ""){printf ("Nobody Removed\n");}else{ print "\n Users Removed:\n" . $list . "\n"; } printf("Users Deleted: %d\n", mysql_affected_rows()); $sqldel = "DELETE Users2, StringyChat, pm, namechanges, kicksamount, broadcast, timeban FROM Users2 LEFT JOIN StringyChat ON Users2.mxitid = StringyChat.StringyChat_ip LEFT JOIN pm ON Users2.mxitid = pm.mxitid LEFT JOIN namechanges ON Users2.mxitid = namechanges.userid LEFT JOIN kicksamount ON Users2.mxitid = kicksamount.mxitid LEFT JOIN broadcast ON Users2.mxitid = broadcast.mxitid LEFT JOIN timeban ON Users2.mxitid = timeban.mxitid WHERE Users2.last_online < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))"; mysql_query($sqldel) or die("Error: ".mysql_error()); //// end of removal/// //// removal of messages//// $query = "DELETE FROM StringyChat WHERE StringyChat_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))"; mysql_query($query); printf("Messages deleted: %d\n", mysql_affected_rows()); //// removal of private messages//// $query1 = "DELETE FROM pm WHERE time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))"; mysql_query($query1); printf("Private Messages deleted: %d\n", mysql_affected_rows()); ?>
  21. I'm trying to show a profile completeness bar on the users account and the progress bar is showing but it's not adding the number values in order to calculate the percentage of completed fields ie: if($row['title'] != '') $completedTitle = 20; My shortened code is as follows: <?php $result = mysql_query("SELECT title,name,surname,identityno,gender FROM cic_candidates WHERE id='$id' LIMIT 1"); while($row = mysql_fetch_assoc($result)) $maximumPoints = 100; { if($row['title'] != '') $completedTitle = 20; if($row['name'] != '') $completedName = 20; if($row['surname'] != '') $completedSurname = 20; if($row['identityno'] != '') $dcompletedIdentityno = 20; if($row['gender'] != '') $completedGender = 20; } $percentage = ($completedTitle+$completedName+$completedSurname+$completedIdentityno+$completedGender)*$maximumPoints/100; echo "".$percentage."%"; ?> The percentage shows in the echo but the total is wrong - it's not taking the values of 20 points for each field that is completed and including them in the "addition" part of the percentage calculation. Please can you tell me where I'm going wrong - I've been trying to figure this out for 4 days and have googled this and read over 2000 forums but can't find the answer. Any help would be greatly appreciated.
  • 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.