SalientAnimal

$result = mysqli_query($link,$sqlsearch)or die(mysqli_error()); while($row = mysqli_fetch_array($result)) { $store_code = $row['store_code']; $outlet_name = $row['outlet_name']; $address_0 = $row['address_0']; $address_1 = $row['address_1']; $address_2 = $row['address_2']; $address_3 = $row['address_3']; $address_4 = $row['address_4']; $contact_number_1 = $row['contact_number_1']; $contact_number_2 = $row['contact_number_2']; $license = $row['scanned_license']; $permit_renewal = $row['scanned_permit_renewal']; $identity_document = $row['scanned_identity_document']; $other_document = $row['scanned_other_document']; echo "<div class='row'>"; echo "<div class='col-md-12'>"; echo "<div class='box box-primary'>"; echo "<div class='box-header with-border'>"; echo "<h3 class='box-title'>Outlet Details</h3>"; echo "<table> <tr> <th>Outlet Details</th> <th>Outlet Documentation</th> </tr> <tr> <td> <table> <tr> <th>Store Code</th> </tr> <tr> <td>".$store_code."</td> </tr> <tr> <th>Store Name</th> </tr> <tr> <td>".$outlet_name."</td> </tr> <tr> <th>Store Address</th> </tr> <tr> <td>".$address_0."</td> </tr> <tr> <td>".$address_1."</td> </tr> <tr> <td>".$address_2."</td> </tr> <tr> <td>".$address_3."</td> </tr> <tr> <td>".$address_4."</td> </tr> <tr> <th>Contact Person</th> </tr> <tr> <td>Jim</td> </tr> <tr> <th>Contact Number</th> </tr> <tr> <td>".$contact_number_1."</td> </tr> <tr> <td>".$contact_number_2."</td> </tr> </table> </td> <td> <table> <tr> <th>License</th> </tr> <tr> <td><a href='uploads/".$license."'>".$license."</a></td> </tr> <tr> <th>Permit Renewal</th> </tr> <tr> <td><a href='uploads/".$permit_renewal."'>".$permit_renewal."</a></td> </tr> <tr> <th>Identity Document</th> </tr> <tr> <td><a href='uploads/".$identity_document."'>".$identity_document."</a></td> </tr> <tr> <th>Other Documents</th> </tr> <tr> <td><a href='uploads/".$other_document."'>".$other_document."</a></td> </tr> <tr> <th>Reference Number</th> </tr> <tr> <td>HSA0123456</td> </tr> <tr> <td>HSA0123457</td> </tr> </table> </td> </table> "; } echo "</table>"; mysqli_close($link); Some code improvements made

Hi All, I'm trying to get my head wrapped around how to create a Table from a DB. I have no issues getting the information, and all the data is pulling through as expected. I just cant seem to get my layout of my table right. I have tried to draw a diagram of how I would like my table to be displayed. The left hand side of the table is pretty much static with the amount of rows always being static. Here there are 5 rows with headings, the 3 row / nested table returning an address section with 5 address rows, and the last row will return the contact number row with 2 contact numbers. The biggest complication is on the right hand side of the table which shows a list of hyperlinked documents, however each document type (heading) may have anywhere from 1 - 10 documents. And I would need this section of the table to expand as each row with a new document is returned. I have attached the layout diagram. My current code for this section looks as follows (The code below is still a work in progress): echo "<div class='row'>"; echo "<div class='col-md-12'>"; echo "<div class='box box-primary'>"; echo "<div class='box-header with-border'>"; echo "<h3 class='box-title'>Outlet Details</h3>"; echo "<table border = '0'>"; echo " <tr> <th width='50%'>Outlet Details</th> <th width='50%'>Outlet Documentation</th> </tr> "; while($row = mysqli_fetch_array($result)) { $store_code = $row['store_code']; $outlet_name = $row['outlet_name']; $address_0 = $row['address_0']; $address_1 = $row['address_1']; $address_2 = $row['address_2']; $address_3 = $row['address_3']; $address_4 = $row['address_4']; $license = $row['scanned_license']; $permit_renewal = $row['scanned_permit_renewal']; $identity_document = $row['scanned_identity_document']; $other_document = $row['scanned_other_document']; echo "<tr> <td>".$store_code."</td> <td><a href='uploads/".$license."'>".$license."</a></td> </tr>"; echo "<tr> <td>".$outlet_name."</td> <td><a href='uploads/".$permit_renewal."'>".$permit_renewal."</a></td> </tr>"; echo "<tr> <td>".$address_0."</td> <td><a href='uploads/".$identity_document."'>".$identity_document."</a></td> </tr>"; echo "<tr> <td>".$address_1."</td> <td><a href='uploads/".$other_document."'>".$other_document."</a></td> </tr>"; }

Somehow with my brain working overtime and not being a seasoned php script writer I didn't even think of doing this, although I know I have done it in the past. Thanks...

Hi All, I am running a PHP script on a daily basis to import data into my database. THe PHP file is executed by my Windows Services. The PHP file is a LOAD DATA IN FILE script and looks like the below: LOAD DATA LOCAL INFILE '\master_import\\myfilename.csv' IGNORE INTO TABLE mydatabase.mytable CHARACTER SET latin1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES ( record_number , name , action_date , location , type_of_outlet .... ) ; The ENCLOSED BY '"' is where my problem lies, as I do not know how to use the '"' without PHP reading it as a " to close the script. Any help will be appreciated. Thanks.

I've made some changes to this now and I have managed to get the entity code from the previous page to echo correctly. But what I can seem to get right is to now use this entity number that is passed to query my database and populate the form: $link = mysqli_connect("localhost", "username", "password", "db"); if(isset($_GET['entity_number'])) { $store_code = $_GET['entity_number'];} echo $entity_number; $sql = mysqli_query ($link,"SELECT id , username , entity_number , entity_name , address_0 , address_1 , address_2 , address_3 , address_4 , contact_number_1 , contact_number_2 FROM scrm.liquor_licenses WHERE entity_number= '$entity_number'"); while ($row = mysqli_fetch_assoc($sql)); $username = $row['username']; $entity_number= $row['entity_number']; $entity_name= $row['entity_name']; /* close connection */ The form which is on the same page as the above query: <input type="hidden" class="form-control" value="<?php ECHO $_SESSION['username']; ?>" name="username" readonly> <div class="form-group"> <label>Outlet Number :</label> <input type="text" class="form-control" value="<?php ECHO $entity_number?>" name="entity_number"> </div> <div class="form-group"> <label>Outlet Number :</label> <input type="text" class="form-control" value="<?php ECHO $entity_name?>" name="entity_name"> </div>

Hi all, I have a pretty basic front end system where specific details are displayed. There is also and edit button next to each record that is displayed however not all info about this record is displayed. When I click edit, I want a query to run on my next page, which will pre-populate the form with information from a query using the reference from the previous page. A basic example is: if(isset($_GET['entity_code'])) echo $entity_code; $sql = "SELECT id , username , region , entity_code , address_0 , address_1 , address_2 , address_3 , address_4 , contact_number_1 , contact_number_2 FROM scrm.entity_details WHERE entity_number= '$entity_number'"; if ($result = $mysqli->query($sql)) { /* fetch associative array */ while ($row = $result->fetch_assoc()) { printf ("%s (%s)\n", $row['outlet_number'], $row['outlet_name']); } } /* close connection */ $mysqli->close(); The entity number is passed successfully from the previous page, however my form is not populating and various I have tried give me a number of different outcomes. The below is where I am trying to populate my form: <input type="hidden" class="form-control" value="<?php ECHO $_SESSION['username']; ?>" name="username" readonly> <div class="form-group"> <label>Entity Number :</label> <input type="text" class="form-control" value="<?php ECHO $row['entity_number']; ?>" name="outlet_number"> </div> <div class="form-group"> <label>Entity Name :</label> <input type="text" class="form-control" value="<?php ECHO $row['entity_name']; ?>" name="outlet_number"> </div> I really don't know where I am going wrong...

Thanks Jacques1, really appreciate the help. And it eliminates having to use the prepared statements and then dropping them after one use.

The prepare statement is to compile the CSV file with a datestamp. I used it this way in MySQL Workbench to compile the export file with the datestamp. From my understanding on the research I did, this was the only way to add a datestamp to a file in MySQL Workbench.

I can't say I am experienced at all in doing out files when it comes to PHP. Doing it under MySQL Workbench is a lot easier, so my question is really the principle of applying it. My understanding in PHP is that the actual query needs to be within Double quotation marks, however this becomes a challenge when taking the existing file / script directly from MySQL Workbench and trying to plug it into PHP. I'm unsure of how to apply this. That said, I'm going to attempt a change based on what you have said, let me see what the outcome is.

The single quote you are referring to has a corresponding single quote after the .csv Although, this does work when run directly on MySQL Workbench, I'm not sure what needs to change for plugging it into PHP. This is what the working script in MySQL Workbench looks like: SET @exportfile = CONCAT("SELECT 'FILED_1' , 'FIELD_2' , .... UNION ALL SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myoutputfile" , DATE_FORMAT( NOW(), '%Y%m%d') , ".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '''' LINES TERMINATED BY '\r\n'" ); PREPARE processing FROM @exportfile; EXECUTE processing; DROP PREPARE processing;

Hi All, This is part of a PHP script that I am wanting to use to automate some daily data exports. The problem I am having, is adding a datestamp to my export file. The script itself works when executed in MySQL but not under PHP. Does anyone have any insight into how I can get this working? The error message I am currently getting is, and the part of php script I am running can be seen below. $sql1 = "SET @exportfile = CONCAT(SELECT 'FIELD_1' , 'FIELD_2' , .... UNION ALL SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myexportfile" , DATE_FORMAT( NOW(), '%Y%m%d') , ".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '''' LINES TERMINATED BY '\r\n' );"; $sql2 = "PREPARE processing FROM @exportfile;"; $sql3 = "EXECUTE processing;"; $sql4 = "DROP PREPARE processing;"; Any help here is greatly appreciated.

Thanks guys, you answers really add a lot of value. And definitely help me in knowing where I need to do a lot more learning. @requinix The scripts would be just about exactly the same in each scenario yes, with the only difference being the column counts and the destination tables as mentioned. The party/s whom need to be notified of a failed / successful script in some instances may also differ, but this would not be often. @Jacques1 I was actually not aware of TWIG, so will be looking into making use of this and learning more about it. I broke the TSL certification, as I could not get any of my settings to work correctly. The "Send" e-mail address I am using at the moment, is also only for a test scenario, and I will be creating the reporting e-mail address as soon as everything is working. I am still running everything on a "Virtual Environment". The column names are luckily not named as listed here no I'm not sure how to answer your other comments

Thanks for your answer here requinix. Basically what I mean by a cleaner script really comes down to understand how to use and populate a mail and then send it via PHP mailer. My current script is made up of a lot of code from various sources, meaning I don't always fully understand what it is doing, and secondly, I have quite a bit of code repetition. Another problem would be that I am possibly using outdated source code. Here is an example of what I have / am using: <?php $sql1 = "USE mydatabase"; $sql2 = "TRUNCATE TABLE mytable"; $sql3 = "LOAD DATA LOCAL INFILE 'DataImport//sourcefile.csv' REPLACE INTO TABLE mytable CHARACTER SET latin1 FIELDS TERMINATED BY ',' IGNORE 1 LINES (`id` , `column1` , `column2` , `column3` , `column4` , `column5` , `column6` , `column7` , `column8` , `column9` , `column10` , `column11` , `column12`);"; $con=mysqli_connect("localhost","root","mypassword","mydatabase"); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); }; $result1 = mysqli_query($con, $sql1); $result2 = mysqli_query($con, $sql2); $result3 = mysqli_query($con, $sql3); if (mysqli_affected_rows($con) > 1) { $message ="". mysqli_affected_rows($con). " rows were successfully added to the table!"; } else { $message = "" . mysqli_error($con). "has caused the updateto fail"; }; echo $message; // To send HTML mail, the Content-type header must be set require("PHPMailerAutoload.php"); // path to the PHPMailerAutoload.php file. require("class.phpmailer.php"); $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; $body = "<html>\n"; $body .= "<body style=\"font-family:Verdana, Verdana, Geneva, sans-serif; font-size:12px; color:#344433;\">\n"; $body = $message; $body .= "</body>\n"; $body .= "</html>\n"; // Additional headers $subject = 'Daily Import Result' ."\r\n"; $mail = new PHPMailer(); $mail->IsSMTP(true); $mail->Mailer = "smtp"; $mail->Host = "smtp.mydomain.co.za"; $mail->Port = 587; // 8025, 587 and 25 can also be used. Use Port 465 for SSL. $mail->SMTPAuth = true; $mail->SMTPSecure = 'tls'; $mail->Username = "myusername@mydomain.co.za"; $mail->Password = "mymailpassword"; $mail->CharSet = "UTF-8"; $mail->SMTPOptions = array( // Bypass security verification on e-mail. 'ssl' => array( 'verify_peer' => false, 'verify_peer_name' => false, 'allow_self_signed' => true ) ); $mail->From = 'reportingserver@mydomain.co.za'; $mail->FromName = 'Reporting Server'; //$mail->AddAddress('mail1@mydomain.co.za', 'Mail1'); //$mail->AddAddress('mail2@mydomain.co.za', 'Mail2'); //$mail->AddAddress('mail3@mydomain.co.za', 'Mail3'); $mail->AddReplyTo('me@mymailserver.com', 'My Name'); $mail->Subject = $subject; $mail->Body = $body."\r\n"; $mail->WordWrap = 50; if(!$mail->Send()) { echo 'Message was not sent.'; echo 'Mailer error: ' . $mail->ErrorInfo; exit; } else { echo 'Message has been sent.'; } mysqli_close($con); ?>

Hi All, I haven't made use of PHPMailer for quite sometime, as I had been in a different environment. I have noticed that the has been quite a few changes since I last used it, and after struggling the whole day to eventually get it working I am not entirely satisfied with the solution. Some background of what I am doing and will be using it for: I have a number of PHP Scripts that are executing through the use of *.bat files through windows Task Manager. The scripts execute running a number of tasks, and at the end of the task I need a mail to be sent with the results of the task i.e. Successful, Failed, Lines added to database etc... The mail will be going to not only myself, but to a number of other users, and eventually I want to expand the use of it. So I would like to achieve the following: A cleaner script Select mail recipients from a database table, so that I don't need to manually input it into each form / script. Send the mails as HTML using base64 for the images / other "beautifying" elements. Understand exactly how to use all / at least most of the tags correctly. I fully understand that many of these answers could probably be found on google, but I am yet to find a source, where the explanation is clear and precise. What I have noticed, is that 95% of coders expect the user to understand coding on the same level as they are on, but it is not always the case, so any help / guidance will really be appreciated. /if need be, I can supply my current code. Thanks.

Would a simple echo statement do the trick? Or should I rather look to ind out more about something a little more complex? Reason I'm asking is because when the list of users is populated I would like to perform a number of functions by simply clicking on the drop down arrow associated with each user. This will then give me 3 links, i.e send a message, edit user, delete user. This is the extent of my current idea, by I can see it extending once I have the basics working