ragrim

Hi, Im looking to add the ability for users to save a search query as a custom report and wondering whats the best ay to do this. For example a user will run a search every month for all users thatexpire in the next 30 days so they can mail out subscription renewel reminders. What i was thinking was adding an option to "save query" after running a custom search and then save the mysql_query in a table in the DB. Then i would have a screen that selects all records from this table and displays the description they chose and when they run it i just grab the string from the DB and it then runs the report. Is this a good way to do it or is there a better way to do it? Sorry if this is the wrong section im not sure if it fell under php or mysql. Cheers.

Hi, Im not sure if this is the right section for this post so aplogies ifit is not. Im building a search form for m DB and the section im searching is across 3 tables with approx 20 fields. im looking for some advice on the best way to go about it. At the moment i have a search form that posts to a search script, currently my script is like this. This isnt the full code, just a sample to make it easier to read what i am doing. $subid = $_POST['sub']; $fname = $_POST['fname']; $lname = $_POST['lname']; $address = $_POST['address']; $suburb = $_POST['suburb']; mysql_query("SELECT * FROM userinfo WHERE subid LIKE '%$subid%' AND FirstNAme LIKE '%$fname%' AND LastName LIKE '%lname%'"); excuse the syntax its probably not correct, im at work and dont hve access to my code. Now this query seems to work fine for the most part, but problem i have is if one of the search fields is NULL i dont get the right results. Im assuming what i need to do is find out which fields are null, then build the query wth the fields that are NOT NULL. Looking for some advice on how to go about this, i have an idea of building the query as a string from php variables and if statements but just incase there is another way to do it i thought i would ask. Cheers.

i think i just realised my problem, because the search is coming from a seperate page, when it goes to page 2 its not getting the search terms, should i be passing it with a GET instead of post?

I have a page setup that paginates 20 results per page and works fine when i do SELECT * or what ever, but when i do a search query it displays fine on page 1, but when i go to page 2 or any other page it displays all results. Im assuming to use pagination i have to load my query into an array or something instead or using a while loop. Im not great with php so any help here would be appreciated. heres my code, i have a search page thats posts to my search results page. <?php include('include/dbconnect.php'); $fname = $_POST['fname']; $lname = $_POST['lname']; $address = $_POST['address']; $suburb = $_POST['suburb']; // find out how many rows are in the table $sql = "SELECT subscribernumbers.SubNumber, userinfo.FirstName, userinfo.LastName, userinfo.Suburb, userinfo.Phone, userinfo.Mobile, userinfo.Email, userinfo.BusinessName FROM userinfo LEFT JOIN subscribernumbers ON userinfo.User_ID = subscribernumbers.FKUserID WHERE FirstName LIKE '%$fname%' AND LastName LIKE '%$lname%' AND Suburb LIKE '%$suburb%'"; $result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = mysql_num_rows($result); //echo $sql; echo "<p class='messagetext'>Query returned " . $numrows . " results</p>"; // number of rows to show per page $rowsperpage = 20; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; $result = mysql_query("SELECT subscribernumbers.SubNumber, userinfo.User_ID, userinfo.FirstName, userinfo.LastName, userinfo.Phone, userinfo.Mobile, userinfo.Email, userinfo.BusinessName, userinfo.Address, userinfo.Suburb FROM userinfo LEFT JOIN subscribernumbers ON userinfo.User_ID = subscribernumbers.FKUserID WHERE FirstName LIKE '%$fname%' AND LastName LIKE '%$lname%' AND Suburb LIKE '%$suburb%' LIMIT $offset, $rowsperpage"); echo "<table id='maintable' align='center' cellspacing='0'>"; echo "<thead class='thead'>"; echo "<th style='width:50px'>Sub Number</th>"; echo "<th style='width:200px'>Name</th>"; echo "<th style='width:200px'>Business Name</th>"; echo "<th style='width:150px'>Phone</th>"; echo "<th style='width:150px'>Mobile</th>"; echo "<th style='width:200px'>Email</th>"; if($address=="") { } else { echo "<th style='width:200px'>Address</th>"; } if($suburb=="") { } else { echo "<th>Suburb</th>"; } echo "</thead>"; while($row = mysql_fetch_array($result)) { ?> <tr onmouseover="ChangeColor(this, true); tooltip.show('<?php echo "<strong>Description: </strong>" . $row['wo_text']; ?>');" onmouseout="ChangeColor(this, false); tooltip.hide();" onclick="DoNav('index.php?page=subscriber_view.php&user=<?php echo $row['User_ID']; ?>');"> <td><?php echo $row['SubNumber'];?></td><td><?php echo $row['FirstName'] . " " . $row['LastName'];?></td><td class="td"><?php echo $row['BusinessName'];?></td><td class="td"><?php echo $row['Phone'];?></td><td class="td"><?php echo $row['Mobile'];?></td><td><?php echo $row['Email']; ?></td> <? if($address=="") { } else { echo "<td>" . $row['Address'] . "</td>"; } if($suburb=="") { } else { echo "<td>" . $row['Suburb'] . "</td>"; } ?> <?php //echo "<td class='td'>" . $row['wo_id'] . "</td><td>" . $row['wo_description'] . "</td><td class='td'>" . $row['priority_description'] . "</td><td class='td' onmouseover='tooltip.show('$test')';' onmouseout='tooltip.hide();'>" . $row['status_description'] . "</td><td class='td'>" . $row['wo_creation_date'] . "</a></td>"; echo "</tr>"; } echo "</table>"; echo "<div class='pagination'>"; /****** build the pagination links ******/ // range of num links to show $range = 3; // if not on page 1, don't show back links if ($currentpage > 1) { // show << link to go back to page 1 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; // get previous page num $prevpage = $currentpage - 1; // show < link to go back to 1 page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; } // end if // loop to show links to range of pages around current page for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { // if it's a valid page number... if (($x > 0) && ($x <= $totalpages)) { // if we're on current page... if ($x == $currentpage) { // 'highlight' it but don't make a link echo " [<b>$x</b>] "; // if not current page... } else { // make it a link echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; } // end else } // end if } // end for // if not on last page, show forward and last page links if ($currentpage != $totalpages) { // get next page $nextpage = $currentpage + 1; // echo forward link for next page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; // echo forward link for lastpage echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; } // end if echo "</div>"; /****** end build pagination links ******/ ?>

Thanks for the reply, That topic seems similar but doesnt seem to help me. i have built a query for this purpose once before but cant remember it. i thought i needed a order by and limit 1 somewhere to make it work.

Hi, I need some help with my query, i have 3 tables, subscribers, subscriber numbers and subscriber transactions. the sub numbers is a lookup table to link transactions and subscribers. what i want is to only pull the last record for each person from subscriber transactions. heres what i have atm. SELECT userinfo.FirstName, userinfo.LastName, subscribernumbers.SubNumber, subtransactions.TransDate, subtransactions.PaymentType FROM userinfo INNER JOIN subscribernumbers ON userinfo.User_ID = subscribernumbers.FKUserID INNER JOIN subtransactions ON subtransactions.FKSubNum = subscribernumbers.SubNumber

Hi, i dont know how to explain this so ill start with what i want to do. Id like to create a simple text based game in php for learning purposes but im stuck on 1 thing, making a page refresh based on a server timer. Basically every 2 minutes is a server tick and at such time your character gets +gold based on resources etc. im just not sure what such a thing would be called so i havnt been able to google it, obviously the ticks need to be server controlled so a user cant just refresh a page to get a new tick. i would also need the page to display how long left till next tick. the only way i can see it possible is to have a server side application controlling timers, and the php page requests time remaining or something. Any help would be muchly appreciated, thanks.

Hi, Ive built a website with an image gallery and when i uploaded it to a webhost i discovered they dont have PDO enabled, so ive tried to change my script to work without PDO but ive had no luck, i was hoping someone could look at my script (which i found on the net) and help me to remove the GDO part from it if its possible. Ive work around it not using PDO but this is the only script i can find to upload thumbnails. any help would be muchly appreciated. <?php if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0) { $fileName = $_FILES['userfile']['name']; $tmpName = $_FILES['userfile']['tmp_name']; $fileSize = $_FILES['userfile']['size']; $fileType = $_FILES['userfile']['type']; $category = $_POST['category']; $username = $_POST['username']; $description = $_POST['description']; $date = date("Y-m-d"); $fp = fopen($tmpName, 'r'); $content = fread($fp, filesize($tmpName)); $content = addslashes($content); fclose($fp); if(!get_magic_quotes_gpc()) { $fileName = addslashes($fileName); } /*** get the image info. ***/ $size = getimagesize($_FILES['userfile']['tmp_name']); /*** assign our variables ***/ $image_type = $size['mime']; $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb'); $image_width = $size[0]; $image_height = $size[1]; $image_size = $size[3]; $image_name = $_FILES['userfile']['name']; $maxsize = 99999999; /*** check the file is less than the maximum file size ***/ if($_FILES['userfile']['size'] < $maxsize ) { /*** create a second variable for the thumbnail ***/ $thumb_data = $_FILES['userfile']['tmp_name']; /*** get the aspect ratio (height / width) ***/ $aspectRatio=(float)($size[0] / $size[1]); /*** the height of the thumbnail ***/ $thumb_height = 200; /*** the thumb width is the thumb height/aspectratio ***/ $thumb_width = $thumb_height * $aspectRatio; /*** get the image source ***/ $src = ImageCreateFromjpeg($thumb_data); /*** create the destination image ***/ $destImage = ImageCreateTrueColor($thumb_width, $thumb_height); /*** copy and resize the src image to the dest image ***/ ImageCopyResampled($destImage, $src, 0,0,0,0, $thumb_width, $thumb_height, $size[0], $size[1]); /*** start output buffering ***/ ob_start(); /*** export the image ***/ imageJPEG($destImage); /*** stick the image content in a variable ***/ $image_thumb = ob_get_contents(); /*** clean up a little ***/ ob_end_clean(); /*** connect to db ***/ $dbh = new PDO ("mysql:host=localhost;dbname=db", 'user', 'pass'); /*** set the error mode ***/ $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); /*** prepare the sql ***/ $stmt = $dbh->prepare("INSERT INTO upload (type, content, thumbnail, image_name, description, uploaddate, size, category) VALUES (? ,?, ?, ?, '$description', NOW(), '$filesize', '$category')"); $stmt->bindParam(1, $image_type); $stmt->bindParam(2, $imgfp, PDO::PARAM_LOB); $stmt->bindParam(3, $image_thumb, PDO::PARAM_LOB); $stmt->bindParam(4, $image_name); /*** execute the query ***/ $stmt->execute(); } else { /*** throw an exception is image is not of type ***/ throw new Exception("File Size Error"); } } else { // if the file is not less than the maximum allowed, print an error throw new Exception("Unsupported Image Format!"); } header( 'Location: admin.php?location=upload.php' ) ; ?>

Hi, I have a page displaying a list of records, i want to display these record based on a combo box which just says to display records with status, open, closed or all. im not sure how to do this and im pretty sure im wrong, but heres what i came up with <?php $sql = ""; if(isset($_POST['submit'])) { $queryvar = $_POST["combo1"]; if ($queryvar == "All") //Echo "You are viewing all records"; $sql = mysql_query("SELECT * FROM tblworkorders WHERE status = ''"); elseif ($queryvar == "Open") //echo "you are viewing all Open records"; $sql = mysql_query("SELECT * FROM tblworkorders WHERE status = 'Open'"); elseif ($queryvar == "Closed") //echo "Viewing all Closed records"; $sql = mysql_query("SELECT * FROM tblworkorders WHERE status = 'Closed'"); } echo "query" . $sql; $con = mysql_connect("localhost","root",""); mysql_select_db(it, $con); $result = $sql; $num=mysql_numrows($result); mysql_close(); ?> The error i am getting is Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\it\ifelsetest.php on line 30 im guessing the $sql variable is not global or something, im not too sure. Thanks in advance

Thats a good start thanks.. Gives me an idea of what to do, ill have a play and report back when it doesnt work lol.

Hi, I have a bit of a helpdesk project i have been working on and so far everything is going well, but i have finally hit a wall.... When created work orders i have a category drop down box, now when a work order is created i would like to send an email to the relevant technician letting them know a new work order is created. we have about 10 categories and 3 technicians. id be happy to hard code it if i need to but ultimately id like some super cool script that does a whole heap of if statements. So here is basically what im after, and i have no idea how to do it as i havnt done many if statements... mysql_query(insert into workorders values summary, category, etc) if category = 1 send email to technician 1 if category = 2 send email to technician 2 if category = 3 send email to all technicians im not sure where to start on this so any help would be appreciated, thanks in advance

Hi, Im trying to update a record from a form but im having some troubles using the variables ans such, i use $_POST to get the info from the field of the form, store it as a variable and try to use the variable in the query like i do with insert and select queries, but i just cant seem to get it to work with update. <?php $fname = $_POST["firstname"]; $lname = $_POST["lastname"]; $address = $_POST["address"]; $suburb = $_POST["suburb"]; $postcode = $_POST["postcode"]; $phone1 = $_POST["phone1"]; $phone2 = $_POST["phone2"]; $email1 = $_POST["email1"]; $email2 = $_POST["email2"]; $billingemail = $_POST["billingemail"]; $host=""; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name=""; // Database name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect server "); mysql_select_db("$db_name")or die("cannot select DB"); $query = 'UPDATE `invoicing`.`customers` SET `billingemail` = '$fname' WHERE `customers`.`id` = 13;'; ?> Any help would be muchly appreciated.

haha so easy, i knew it had to be something simple. thanks heaps for the qucik reply

Hi, im trying to display mysql results in a form but for some reason im only getting the first field and not the rest, it will show me the "firstname" field but nothing else, for the life of me cant figure out why, any help would be appreciated. <?php $sql = mysql_query("SELECT * FROM invoicing.customers WHERE id = '$id' "); $sql_res = mysql_query($sql); //execute query while($row = mysql_fetch_array($sql)) $firstname=$row['firstname']; $lastname=$row['lastname']; $address=$row['address']; $suburb=$row['suburb']; $postcode=$row['postcode']; $phone1=$row['phone1']; $phone2=$row['phone2']; $email1=$row['email1']; $email2=$row['email2']; $billingemail=$row['billingemail']; ?> and then i display it in forms with <td>First Name<input name="firstname" style="width: 191px" type="text" class="style15" value="<?php print "$firstname" ?>"></td> <td>Last Name<input name="lastname" type="text" class="style14" value="<?php print "$lastname" ?>" style="width: 191px"> </td> has it got something to do with the fact im using $row?

first of all sorry if this is in the wrong section, i wasnt sure where to post it. im trying to insert a date into mysql from a html form but it just ends up adding 0000-00-00 in the date field no matter what i do, ive been trying to figure this out for weeks. my form use a date picker in dd-mm-yyyy format and i pass it to a php script with $_POST and that works fine, but putting the data into mysql never works. do i need to convert the date format somehow? i need the form to be in dd-mm-yyyy format cause im in australia and thats about the only way we can read it over here. any help on this would be muchgly appreciated, thank you.

thank you very much, i got it working! now i just gotta learn how to format it all haha!

this may help, { echo "<b>User</b> :{$row['user']} <br>" . "<b>O/S</b> : {$row['os']} <br>" . "<b>MAC</b> : {$row['mac']} <br>" . "<b>IP</b> : {$row['ip']} <br>" . "<b>Asset Name</b> : {$row['asset_name']} <br><br>"; echo "<a href="edit_info.php?{$row['user']}"> EDIT!"; } ?> as you can see im trying to send the value of user to the next page. but i cant seem to get it right.

ok i hit a wall allready lol. i know how to pass a variable from one page to another, but how do i send the variable of the current record thats open? say i use the "user" as my unique identifier, how can i add a link to send that "user" variable?\ if im making any sense lol

Thank you, this is the part i needed, i just couldnt get the right syntax for this piece of code, now that works i should be able to get the rest of it, ill see how i go. //put the information in text boxes echo "User: <input type='text' name='user' value='{$row['user']}'>"; PS: why does the insert code and quote not work for me? :S

Hi, apologies for the newbie question hehe, but im stuck and i just cant find the answer to what will be the easier question ever i have website that has pictures on it and when you click one it loads a php frame which displays info about the page, now what i want is an edit button which then sends the info of the mysql record i was just looking at into a form which i can then edit and update, now i know how to delete record, add records etc etc, but i just dont know how to load a record into a form! by form i mean an editible text field. so heres what i got <? include 'config.php'; include 'open_db.php'; $query = "SELECT user, os, asset_name, mac, ip FROM inventory"; $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "<b>User</b> :{$row['user']} <br>" . "<b>O/S</b> : {$row['os']} <br>" . "<b>MAC</b> : {$row['mac']} <br>" . "<b>IP</b> : {$row['ip']} <br>" . "<b>Asset Name</b> : {$row['asset_name']} <br><br>"; } ?> So all i want to be able to do is click an edit link on that page which then takes the info i have loaded and puts it into a form. should be pretty easy hehe. Thanks in advance