Jump to content

Search the Community

Showing results for tags 'sql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • 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

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

Found 140 results

  1. i was working on a mini project that imports csv file to the database through ajax and it's working fine <?php if(!empty($_FILES["marks_file"]["name"])) { $connect = mysqli_connect("localhost", "root", "", "dbname"); $output = ''; $allowed_ext = array("csv"); $extension = end(explode(".", $_FILES["marks_file"]["name"])); if(in_array($extension, $allowed_ext)) { $file_data = fopen($_FILES["marks_file"]["tmp_name"], 'r'); fgetcsv($file_data); while($row = fgetcsv($file_data)) { $name = mysqli_real_escape_string($connect, $row[0]); $Physics = mysqli_real_escape_string($connect, $row[1]); $Maths = mysqli_real_escape_string($connect, $row[2]); $Chemistry = mysqli_real_escape_string($connect, $row[3]); $Biology = mysqli_real_escape_string($connect, $row[4]); $SST = mysqli_real_escape_string($connect, $row[5]); $query = " INSERT INTO csv (name, Physics, Maths, Chemistry, Biology, SST) VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST') "; mysqli_query($connect, $query); } $select = "SELECT * FROM csv ORDER BY id DESC"; $result = mysqli_query($connect, $select); $output .= ' <table class="table table-bordered"> <tr> <th width="25%" >name</th> <th width="15%" >Physics</th> <th width="15%" >Maths</th> <th width="15%" >Chemistry</th> <th width="15%" >Biology</th> <th width="15%" >SST</th> </tr> '; while($row = mysqli_fetch_array($result)) { $output .= ' <tr> <td>'.$row["name"].'</td> <td>'.$row["Physics"].'</td> <td>'.$row["Maths"].'</td> <td>'.$row["Chemistry"].'</td> <td>'.$row["Biology"].'</td> <td>'.$row["SST"].'</td> </tr> '; } $output .= '</table>'; echo $output; } else { echo 'errorx'; } } else { echo "errory"; } ?> however the imported csv files inserts null values in the tables because the format of all csv files assigned to me are in the exact same format: ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 i changed my code a bit modified the functions to espace blank spaces and not return null values in the data tables while (($row = fgetcsv($file_data, 1000, ",")) !== FALSE) { if ((string) $row[0] != '0' and empty($row[0])) { continue; } $name = mysqli_real_escape_string($connect, $row[0]); $Physics = mysqli_real_escape_string($connect, $row[1]); $Maths = mysqli_real_escape_string($connect, $row[2]); $Chemistry = mysqli_real_escape_string($connect, $row[3]); $Biology = mysqli_real_escape_string($connect, $row[4]); $SST = mysqli_real_escape_string($connect, $row[5]); $query = " INSERT INTO csv (name, Physics, Maths, Chemistry, Biology, SST) VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST') "; mysqli_query($connect, $query); } But it doesn't work with the specified csv files i think the ajax call breaks and that's why nothing happens.However it works fine with csv files without blank spaces
  2. I have a successful select query, where I'm looping and building a row for getting parameters which works correctly while ($row = $orderDetailCheck->fetch(PDO::FETCH_ASSOC)) { $params = [ ":INVOICE_NUMBER" => $row["INVOICE_NUMBER"], ":CUSTNM" => $row["CUSTNM"], ":SELLINGN" => $row["SELLINGN"], ":GROUP" => $row["GROUP"], ":DTL12" => $row["DTL12"], ":DTL13" => $row["DTL13"], ":QUANTITY" => $row["QUANTITY"], ":COUNT_PLMN_1" => $row["COUNT_PLMN_1"], ":LAST_DATE" => $row["LAST_DATE"], ]; } My issue now is that, for each row, I need to perform two merges because the data from that select is going to be split into two tables in db2. Some values are truly split between the tables but some values are shared between the two. I'm not sure the best way to perform these two merges because if the first one (products table) inserts, then it creates an ID that I need as a foreign key basically, to insert into the orders table. So on insert I need to grab that newly created ID and use it for ```product_id``` in the second merge. If the first merge performs the update when matched, then I need to grab the existing ID for that record so that I can update the proper record in the orders table. My two merge statements: /*products table*/ MERGE INTO products AS P USING(VALUES( :GROUP, :DTL12, :DTL13, :CUSTNM, :SELLINGN, :COUNT_PLMN_1, :LAST_DATE ) ) AS S(GROUP,DTL12,DTL13,CUSTNM,SELLINGN,COUNT_PLMN_1,LAST_DATE) ON s.GROUP = p.GROUP and s.DTL12 = p.DTL12 and s.DTL13 = p.DTL13 and s.CUSTNM = p.CUSTNM WHEN MATCHED THEN UPDATE SET LAST_DATE = s.LAST_DATE WHEN NOT MATCHED THEN INSERT VALUES (s.GROUP,s.DTL12,s.DTL13,s.CUSTNM,s.SELLINGN,s.COUNT_PLMN_1,s.LAST_DATE) /*ORDERS Table*/ MERGE INTO ORDERS AS PO USING(VALUES( /*need foreign key, which is id from products table*/ :QUANTITY, :LAST_DATE, :INVOICE_NUMBER )) AS S(PRODUCT_ID,quantity_ordered,LAST_DATE,invoice_number) ON s.PRODUCT_ID = po.id WHEN MATCHED THEN UPDATE SET LAST_DATE = s.LAST_DATE, quantity_ordered = s.quantity_ordered, invoice_number = s.invoice_number WHEN NOT MATCHED THEN INSERT VALUES (s.PRODUCT_ID, s.quantity_ordered, s.LAST_DATE, s.invoice_number) Examples: If my ROW Returns INVOICE | CUSTNM | SELLINGNUM | GROUP | DTL12 | DTL13 | QUANTITY | COUNT_PLMN_1 | LAST_DATE ================================================================================================================== 1122 123 321 995 1452 12 13 5 '2018-12-14' Then my insert into products would be products ID | GROUP | DTL12 | DTL13 | CUSTNM | SELLINGNUM | COUNT_PLMN_1 | LAST_DATE ========================================================================================================== 1 995 1452 12 123 321 5 '2018-12-14' and my insert to orders: ORDERS PRODUCT_ID | QUANTITY_ORDERED | LAST_DATE | INVOICE ============================================================ 1 13 '2018-12-14' 1122 But if my next record matched on my unique qualities with a new invoice, quantity and date ROW Returns INVOICE | CUSTNM | SELLINGNUM | GROUP | DTL12 | DTL13 | QUANTITY | COUNT_PLMN_1 | LAST_DATE ================================================================================================================== 1133 123 321 995 1452 12 4 5 '2018-12-18' Then I would update products like so: products ID | GROUP | DTL12 | DTL13 | CUSTNM | SELLINGNUM | COUNT_PLMN_1 | LAST_DATE ========================================================================================================== 1 995 1452 12 123 321 5 '2018-12-18' and update orders like so: ORDERS PRODUCT_ID | QUANTITY_ORDERED | LAST_DATE | INVOICE ============================================================ 1 4 '2018-12-18' 1133 I guess the main question is: How can I get the ID of a record from the products table (whether it's an existing match OR newly created in the merge) and once I get it, how can I use it for the 2nd merge?
  3. I have a php script that I've been running that seems to have been working but now I'm wondering if some of my logic is potentially off. I select records from a db table within a date range which I put into an array called ```$validCount``` If that array is not empty, that means I have valid records to update with my values, and if it's empty I just insert. The trick with the insert is that if the ```STORES``` is less than the ```Quantity``` then it only inserts as many as the ```STORES``` otherwise it inserts as many as ```Quantity```. So if a record being inserted with had Stores: 14 Quantity:12 Then it would only insert 12 records but if it had Stores:1 Quantity:20 It would only insert 1 record. In short, for each customer I should only ever have as many valid records (within a valid date range) as they have stores. If they have 20 stores, I can have 1 or 2 records but should never have 30. It seems like updating works fine but I'm not sure if it's updating the proper records, though it seems like in some instances it's just inserting too many and not accounting for past updated records. This is the logic I have been working with: if(!empty($validCount)){ for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $updateRslt = $update->execute($updateParams); }catch(PDOException $ex){ $out[] = $failedUpdate; } } }else{ if($row2["QUANTITY"] >= $row2["STORES"]){ for($i=0; $i<$row2["STORES"]; $i++){ try{ $insertRslt = $insert->execute($insertParams); }catch(PDOException $ex){ $out[] = $failedInsertStore; } } }elseif($row2["QUANTITY"] < $row2["STORES"]){ for($i=0; $i<$row2["QUANTITY"]; $i++){ try{ $insertRslt = $insert->execute($insertParams); }catch(PDOException $ex){ $out[] = $failedInsertQuantity; } } } } Let's say customer 123 bought 4 of product A and they have 10 locations customerNumber | product | category | startDate | expireDate | stores ---------------------------------------------------------------------------------- 123 1 A 2018-08-01 2019-03-01 10 123 1 A 2018-08-01 2019-03-01 10 123 1 A 2018-08-01 2019-03-01 10 123 1 A 2018-08-01 2019-03-01 10 Because they purchased less than their store count, I insert 4 records. Now if my ```$validCheck``` query selects all 4 of those records (since they fall in a valid date range) and my loop sees that the array isn't empty, it knows it needs to update those or insert. Let's say they bought 15 this time. Then I would need to insert 6 records, and then update the expiration date of the other 9 records. customerNumber | product | category | startDate | expireDate | stores ---------------------------------------------------------------------------------- 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 There can only ever be a maximum of 10 (store count) records for that customer and product within the valid date range. As soon as the row count for that customer/product reaches the equivalent of stores, it needs to now go through and update equal to the quantity so now I'm running this but it's not running and no errors, but it just returns back to the command line $total = $row2['QUANTITY'] + $validCheck; if ($total < $row2['STORES']) { $insert_count = $row2['QUANTITY']; $update_count = 0; } else { $insert_count = $row2['STORES'] - $validCheck; // insert enough to fill all stores $update_count = ($total - $insert_count); // update remainder } for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $updateRslt = $update->execute($updateParams); }catch(PDOException $ex){ $failedUpdate = "UPDATE_FAILED"; print_r($failedUpdate); $out[] = $failedUpdate; } } for($i=0; $i<$insert_count; $i++){ try{ $insertRslt = $insert->execute($insertParams); }catch(PDOException $ex){ $failedInsertStore = "INSERT_STORE_FAILED!!!: " . $ex->getMessage(); print_r($failedInsertStore); $out[] = $failedInsertStore; } }```
  4. 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(); ?>
  5. 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.
  6. ingerNorway

    Checkbox-1/0-into database

    Hi I wondering what i did wrong here: I wont to register 1 or 0 into database. Checkbox file: <?php include("db-tilkobling.php"); $sqlSetning="SELECT * FROM Kvarterbestilt ORDER BY Møtt;"; $sqlResultat=mysqli_query($db,$sqlSetning) or die ("Ikke mulig å hente data fra databasen"); $antallRader=mysqli_num_rows($sqlResultat); print("<input type='checkbox' name='møtt' value='$møtt'>"); ?> Register-møtt.php <?php include("start.html"); ?> <br/> <p>Registrer Møtt/Ikke møtt ved å fylle ut skjemaet under</p> <form method="post" action="" id="registrerAnsattSkjema" name="registrerAnsattSkjema"> <fieldset> <legend>Registrer møtt / ikke møttt</legend> <label for="personnummer">Personnummer</label> <?php include("listeboks-personnummer.php"); ?><br/> <label for="timenummer">Timenummer</label> <?php include("listeboks-timenummer.php"); ?><br/> Sjekk av denne boksen hvis pasient har møtt til time:<br/> <?php include("checkbox-møtt.php"); ?><br/> <input type="submit" name="registrerMottKnapp" id="registrerMottKnapp" value="Registrer møtt/ikke"> <input type="reset" name="nullstill" id="nullstill" value="nullstill"> </fieldset> </form><br/> <?php /* include("valider-mott.php");*/ $registrerMottKnapp=$_POST ["registrerMottKnapp"]; if ($registrerMottKnapp) { $personnummer=$_POST["Personnummer"]; $timenummer=$_POST["Timenummer"]; $møtt=$_POST["Møtt"]; } if ($møtt == '1') { $query = mysql_query("INSERT INTO Kvarterbestilt(Møtt) VALUES('1')"); } /* $lovligFornavn=validerFornavn ($fornavn); $lovligFornavn2=validerFornavn2 ($fornavn); $lovligFornavn3=validerFornavn3 ($fornavn); $lovligEtternavn=validerEtternavn ($etternavn); $lovligEtternavn2=validerEtternavn2 ($etternavn); $lovligEtternavn3=validerEtternavn3 ($etternavn); $lovligYrke=validerYrke ($yrke); $lovligYrke2=validerYrke2 ($yrke); $lovligYrke3=validerYrke3 ($yrke); if (!$lovligFornavn) { print("Fornavn er ikke fylt ut! <br/>"); } else if (!$lovligFornavn2) { print("Bare bokstaver og mellomrom er tillat; $fornavn<br/>"); } else if (!$lovligFornavn3) { print("Ingen tall tillat; $fornavn<br/>"); } else if (!$lovligEtternavn) { print("Etternavn er ikke fylt ut!<br/>"); } else if (!$lovligEtternavn2) { print("Bare bokstaver og mellomrom er tillat; $etternavn<br/>"); } else if (!$lovligEtternavn3) { print("Ingen tall tillat; $etternavn<br/>"); } else if (!$lovligYrke) { print("Yrke er ikke fylt ut!<br/>"); } else if (!$lovligYrke2) { print("Bare bokstaver og mellomrom er tillat; $yrke<br/>"); } else if (!$lovligYrke3) { print("Ingen tall tillat; $yrke<br/>"); } */ /* include("db-tilkobling.php"); $check=mysqli_query($db,"SELECT * FROM Kvarterbestilt WHERE Personnummer='$personnummer' AND Timenummer='$timenummer'"); $checkrows=mysqli_num_rows($check); if($checkrows>0){echo "Ansatt eksiterer fra før";} else { $sqlSetning="INSERT INTO Kvarterbestilt(Møtt) WHERE Personnummer='$personnummer' VALUES ('$møtt');"; mysqli_query ($db,$sqlSetning) or die ("Ikke mulig Ã¥ registrere i db"); print ("Det er registrert at pasienten har $møtt til timen $timenummer"); } } */ include("slutt.html"); ?>
  7. shaddf

    inserting using case else

    iam trying to insert a summary into a table.Is it possible to write a case condition inside an insert statement like so: insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('5','6','7','8','9', CASE WHEN homeid = l_team_id THEN teamcat_id=1 END , CASE WHEN l_team_id = awayid THEN teamcat_id=2 ELSE teamcat_id= 0 END ); Is what iam trying to do possible??What is the best way to do it???
  8. I have a PHP while loop that pulls from an SQL database and displays the contents in a table with two columns. // Check Connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // Select Data Table $result = mysqli_query($con,"SELECT * FROM Recommendations") or die(mysqli_error); // Split Data $mid = ceil(mysqli_num_rows($result)/2); // Display Content while ($rows = mysqli_fetch_array($result)) { $Name = $rows['Name']; $Author = $rows['Author']; $Cover = $rows['Link to Cover']; $Link = $rows['Link to Profile']; echo "<table><tr><td> <a href='" . $Link . "' >$Name</a> <br /> $Author <br /> <a href='" . $Link . "' ><img src='" . $Cover . "' /></a> </td> <td> <a href='" . $Link . "' >$Name</a> <br /> $Author <br /> <a href='" . $Link . "' ><img src='" . $Cover . "' /></a> </td></tr></table>"; } ?> I want to be able to display the looped results side by side in columns of two. Example: 1 2 3 4 5 6 I've tried using pseudo classes to display only the even and odd results in the different table columns, but honestly have no idea how to do this. I'm new to PHP, so my apologies if the results are really obvious. Thanks in advance!
  9. 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?
  10. demeritrious

    SQL Query Help

    I hope this is the right section of the forum for sql query help. I have three different tables in my phpmyadmin database for my website (Topics, Message, MessageReply). Users will have the ability to post messages within a topic and also respond to messages posted within each topic. I am now stuck trying to write a query that will display all of the topics created in one column and all Messages + Message Replies to those topics in a 2nd column. Please help. thanks,
  11. Im struggling with how to filter items that have multiple categorises say if i wanted to search for a shoe that is good for walking and hiking in my database? Shoe_id Shoe name color_id type_id colors color_id color type type_id type My problem is what do i do in a situations like this? say a shoe is good for walking and running? aka type 1 and 2 shoe id 1 shoe name nike color id 1 type id 1 and 2
  12. Hi all, I was hoping you could point me in the right direction, I have 2 questions. 1. I want to have an option drop down which uses the array of a returned sql query. I have the following code which uses onchange to submit the selection, how do I use the mysql_fetch_array($result1) to give a list of the two selected columns in the $Site=array() bit. 2. The selected result will be used in more SQL select statements, so once a site from the list is selected the relevant data from other tables will be displayed using e.g. select person from people [some inner join statement] where site="site 1"; Therefore the $_POST value must be available to be passed to other queries once selected. Thanks for any help, I still feel like a noob, but I'm getting there. Gary <?php include 'header.php'; ?> <div class='container'> <?php include 'menu.php'; ?> <?php include 'connect.php'; ?> <?php $sql1="SELECT Sites.Site_ID, Sites.Site_name_1 FROM `Sites`"; $result1=mysql_query($sql1); ?> <?php function get_options() { $site=array('Site 1'=>'Site 1', 'Site 2'=>'Site 2', 'Site 3'=>'Site 3'); $options=''; while(list($k,$v)=each($site)) { $options.='<option value="'.$v.'">'.$k.'</option>'; } return $options; } if(isset($_POST['site'])) { echo $_POST['site']; } ?> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST"> <select name="site" onchange="this.form.submit();"> <?php echo get_options(); ?> </select>
  13. I want to display 10 products from each category from database, I started to display the categories but how to make displaying the products from each one. Here is the code I make so far. I'm not sure am I doing it right with doble sql select , or it can be done only with one. <div class="inner shadow"> <?php $query = 'SELECT id, title_bg AS `title_cat` FROM categories'; $result = $this->db->query($query); ?> <?php foreach ($result->result() as $row): ?> <?php $title_cat = stripslashes($row->title_cat); ?> <div class="prod-sec"> <div class="prod-head"> <h2><?= $title_cat?></h2> <div class="clear"></div> </div> <?php $query1 = 'SELECT t1.id, t1.title_bg AS `Title`, t1.text_bg AS `Text`, t1.price, t1.discount, t1.category_id, t1.promo_page, t2.id AS FileID, t2.ext, FROM products t1 LEFT JOIN products_pictures t2 ON t1.id = t2.object_id LEFT JOIN categories t3 ON t3.id = t1.category_id WHERE t1.promo_page = 0 AND t1.is_active = 1 AND t3.title_bg = '$title_cat' ORDER BY RAND() LIMIT 10'; $result1 = $this->db->query($query1); ?> <div id="classeslist2"> <ul class="home_middle_products"> <?PHP foreach($result1->result() as $row1) { $f = 'files/products_first_page/' . $row1->id . '.jpg'; if(is_file(dirname(__FILE__) . '/../../' . $f)) { $img = site_url() . "files/products_first_page/".$row1->id.".jpg"; } else { $img = site_url() . "files/products/".$row1->id."/".$row1->FileID."_2.".$row1->ext; } $title = stripslashes($row1->Title); $text = character_limiter(strip_tags(stripslashes($row1->Text)),250); $title_url = getLinkTitle($title); $link = site_url()."products/product/".$row1->id."/{$title_url}"; ?> <li style="width: 185px; height: 270px; margin-left: 3px; margin-top: 10px;"> <div class="thumb"> <a href="<?=$link?>"><img src="<?=$img?>" alt="<?=$title?>" width="182" /></a> <div class="price"><?PHP echo product_price($row1, array('show_discount' => false,"show_old_price"=>false, 'show_label' => false, 'view' => 'no')); ?> </div> </div> <h2></h2> <h2><a href="<?=$link?>"><?=$title?></a></h2> <? $text = substr($text, 0, 100); ?> <? if (strlen($text) == 100) $text .= '...' ; ?> <p><?=$text?></p> </li> <?php } ?> <div class="clear"></div> </ul> </div> </div> <?php endforeach; ?> <div class="clear"></div> </div>
  14. 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?
  15. 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.
  16. I have two functions, one loading data and the other loading the data into an html table. I've done this for 3 other data sets without issue but this one is not loading into the table for some reason. The html table loads but it's empty. I have error printing on but there are no errors. Also the query returns the correct results when I manually run it so I know it's getting results, plus it's printing the result resource ID. So my result prints, and my html table shows up, but the results are not in the table so I'm assuming maybe I'm not passing it to the table properly? Any guidance here is greatly appreciated //Function to gather data private function loadActivity($cust) { $this->dbConnect(); //loading set data values from top of function file $fromC = $this->cy_from; $fromP = $this->py_from; $thruC = $this->cy_thru; $thruP = $this->py_thru; $endYr = $this->py_endYr; $query = " select count(*), 'PRIOR' as Range from actvty where cust = {$cust} AND date between '{$fromP}' and '{$thruP}' union all select count(*), 'CURRENT' as Range from actvty where cust = {$cust} AND date between '{$fromC}' and '{$thruC}' union ALL select count(*), 'FULL' as Range from actvty where cust = {$cust} AND date between '{$fromP}' and '{$endYr}'"; $result = odbc_exec($this->ocon,$query); print_r($result); //This prints "Resource ID #76" $this->activitySum = array(); if ($result) { while ($row = odbc_fetch_array($result)) { $this->activitySum[$row['Range']]; $this->isloaded = true; } }else{ echo"query failed" . odbc_error(); } } public function getCallActivityHTMLcust($cust, $header='Activity'){ $this->loadActivity($cust); $h2 = "<table class='customer-table'>"; $h2 .= "<thead><tr><th colspan='2' style='font-weight: bold'></th>" . htmlspecialchars($header) . "</tr></thead>"; $h2 .= "<tbody>"; $h2 .= "<tr><td>{$this->pyyy} YTD</td><td style='text-align: right;'>" . $this->activitySum['PRIOR'] . "</td></tr>"; $h2 .= "<tr><td>{$this->yyyy} YTD</td><td style='text-align: right;'>" . $this->activitySum['CURRENT'] . "</td></tr>"; $h2 .= "<tr><td>{$this->pyyy} Full Year</td><td style='text-align: right;'>" . $this->activitySum['FULL'] . "</td></tr>"; $h2 .= "</tbody></table>"; return $h2; }
  17. I have created a web page where my customers will be able to enter details of all items there return back to my store. I have created in such a way that, 1 customer can enter his personal details once but can add any number of items he is returning. I have created a "add more" button to do this job. I have used arrays for this job, but when I am testing my site, database is not taking the items entered and is not displayed by phpMyAdmin. index.php.txt
  18. Hi all. How can i send mail to multiple users at the same time with each user getting their own related data. I'm using php mailer as an engine to send the mail. Based on this, i'd have loved to setup a cron for it but i do not know how, so i figure i'd just do it manually before i get to know how to setup a cron job. It's just to send a reminder to users and each user has a different subscription expiry time. I want each user to get their respective expiration date, expiry day etc. Thanks if(isset($_POST['send_reminder'])){ $sql = "SELECT * FROM users WHERE status = '$status'"; $stmt = $pdo->query($sql); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)){ $name = $row['name']; $acct_no = $row['acct_no']; $email_addresses = $row['email']; $expiry_date = $row['expiry_date']; $expiry_day = $row['expiry_day']; } $message="Hello $name,<br> <p> This is to remind you that your subscription will expire in $expiry_day. </p> <p> Details as follows: Name: $name<br> Account Number: $acct_no<br> Email: $email_addresses<br> Expire in days: $expiry_day<br> Expiry Date: $expiry_date </p> <p> Thank you </p> $mail = new PHPMailer; //$mail->SMTPDebug = 3; // Enable verbose debug output $mail->isSMTP(); // Set mailer to use SMTP $mail->Host = 'mail.server.com'; // Specify main and backup SMTP servers $mail->SMTPAuth = true; // Enable SMTP authentication $mail->Username = 'mails@services.cap'; // SMTP username $mail->Password = 'password'; // SMTP password $mail->SMTPSecure = 'ssl'; // Enable TLS encryption, `ssl` also accepted $mail->Port = 465; // TCP port to connect to $mail->From = mails@services.cap'; $mail->FromName = 'Club 404'; $mail->addAddress($email_addresses); // Add a recipient $mail->WordWrap = 587; // Set word wrap to 50 characters $mail->AddEmbeddedImage("../img/logo.png", "my_logo"); $mail->isHTML(true); // Set email format to HTML $mail->Subject = 'REMINDER ON CLUB EXPIRY DATE'; $mail->Body = $mess; $mail->send(); }
  19. 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.
  20. Aero77

    mysql query question

    I'm trying to make a diesel consumption scoreboard for the drivers in our company, but not sure how I can sort the drivers by average fuel consumption. I have two database tables, one for the members and one for the fuel data. I'm using this sql query to calculate the average, but I would like to have some more information in the same query and also a way to easily sort the members by their average fuel consumption to complete the scoreboard. SELECT 10 * SUM(liter) / (MAX(km) - MIN(km)) as AvgFuel FROM diesel WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid']."; In the diesel table there are information like km, liters, dates, locations etc. How can I extend this query to have more information instead of having multiple queries ? (I'm a newbie) Thanks for reading!
  21. Hello, I am new to this site and hope I am posting this in the right section... The thing is..... I would like some help with php and mysql.. What I want to create is a page where users can create an account by entering their name, email and password, and if the username is taken they will have to try again otherwise their details will be registered to the mysql table and have an account to login. Also I would like a redirect so if the user is a standard user it goes to say “home”, but if the user is an admin it will go to another page ie “home2”, and how would I create the admin in the same table on mysql or seperate? Right the last thing is once the user can register and login, so can the admin, for instance if there are 10 users, when logged in I want each of those users to see different information can this be done and how could I do this ? I thought maybe have the table and the id would be from 1-10 so if user is 1 being dan show his column, and if user logged 2 being steve show column id 2. Can somebody help me implement this please ? I am currently learning php and mysql, im in university I managed to learn how to create the table, connect login, search, add to the database when logged in and edit etc.. but for what I want to achieve now I cannot find any tutorials or help, could somebody please help me with the coding for this and what pages are what and how to create the table in mysql being either 1 table for both user and admin, or 2 tables for separate users and admin. Thanks for your help in advance guys
  22. I am just new to relational algebra probably a pre-step before learning SQL queries. Can you help me make the expressions of relational algebra expression for each of the following queries. This is the table contained inside a bus driver database. driver ( driver_id, driver_name, age, rating ); bus ( bus_id, bus_name, color); reserves ( driver_id, bus_id, date); a. Find the names of drivers who have reserved at least three busses. b. Find the names of drivers who have reserved all busses. c. Find the names of drivers who have reserved all busses called Shuttle. d. Find the IDs of drivers whose rating is better than some driver called Paul. I would be grateful if somebody can help me here.
  23. I have this query : select GROUP_CONCAT(Other_type)as projtype2,PrT_id from _Otherproject_typetbl,_project_typetbl where _Otherproject_typetbl.Other_id=_project_typetbl.Other_id and Project_id=in_projid; and it gives this result: +-----------+--------+ | projtype2 | PrT_id | +-----------+--------+ | All_other | HR_3 | +-----------+--------+ 1 row in set (0.04 sec) I would to set the value incase it is "Allother" to be set to empty or null in any of the columns +-----------+--------+ | projtype2 | PrT_id | +-----------+--------+ | null | HR_3 | +-----------+--------+ 1 row in set (0.04 sec)
  24. Good day, I want to know if I can save the first entry and delete the remaining duplicate enty. This is what I did to get all the records with duplicate entry SELECT a.*, b.totalCount AS Duplicate FROM table a INNER JOIN ( SELECT email, COUNT(*) totalCount FROM psuser GROUP BY email HAVING COUNT(*) >= 2 ) b ON a.email = b.email ORDER BY a.email,a.userId Sample record result ID , EMAIL 100 , A 101 , A 102 , A 200 , B 222 , B 235 , C 262 , C 276 , D 278 , D 300 , E 301 , E 302 , E 305 , E Don't delete the first entry and delete all those remaining. Can I do that?
  25. What I want to do is what is in the shown in the table. I want to only get the Math subject and order the units from unit 1 (UI) to the last available unit in the database; after this, under each unit, to show the homework related to each unit. I have tried to save it into an array with mysql_fetch_array but it stores the row that involves one unit. I want all the units and only the units with their homework under them. My database is attached to this post in a txt file. hope someone can help. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Documento sin título</title> </head> <body> <form action="" method="post" name="form1" id="form1"> <table width="200" border="1"> <tr> <td>Subject:</td> <td>Math</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td> </td> <td>UI</td> <td>UI</td> <td>UII</td> <td>UIII</td> <td>UII</td> <td>...</td> </tr> <tr> <td>Home work</td> <td>Math work1</td> <td>Math work2</td> <td>Math work3</td> <td>Math work4</td> <td>Math work5</td> <td>...</td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> </table> </form> </body> </html>
×

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.