|
PHP - Php Mysql Select Smallest Accending Difference Between Two Variables
Hi everyone,
I have this php code, which connects to a mysql database (through dbconnect.php) and is meant to select the lowest difference between two variables. I can't get very far. The most valuable differences are not the lowest per se, as much as the closest to 0, either positive or negative. Both numbers are positive integers. The page dictates the relative 'ranking' of the page, by multiplying the rank by 20, so that with each successive page, the retrospective line is 20 higher in ranking. The section 20*$page+x is not a value, but a ranking, so the instance would by, in words 'select the lowest difference between two numbers. If the number closest to 0 cannot be selected, can we find the highest value out of the two variables and subtract the lower (they are interchangeable) Code: [Select] <?php $page = $_GET['page']; include("dbc.php") //connect to mysql database $result = mysql_query("SELECT * FROM posts {WHERE (ROW1-ROW2) = 20*$page +1}"); // { indicates my unsure section while($row = mysql_fetch_array($result)){ $id = $row['id']; $username = $row['id']; $sum1 = $row['id']; $sum2 = $row['id']; } $result1 = mysql_query("SELECT * FROM posts {WHERE (ROW1-ROW2) = 20*$page +2}"); // { indicates my unsure section while($row1 = mysql_fetch_array($result1)){ $id2 = $row['id']; $username2 = $row['id']; $2sum1 = $row['id']; $2sum2 = $row['id']; } //this continues with the mysql $page+x increasing successively ?> Also, could we apply a formula that would increase the weighting of those with a higher sum. Thanks a lot, this is really over my head Jack Similar TutorialsThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=310181.0 $var = @$_GET['q'] ; $trimmed = trim($var); $table = @$_GET['field']; $query="SELECT * FROM contacts WHERE @'table' contains @'trimmed' order by id"; $result=mysql_query($query); $num=mysql_numrows($result); Why wont this work? Zacron Not sure why this isnt working. Code: [Select] <?php session_start(); ?> <!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> <script src="http://js.nicedit.com/nicEdit-latest.js" type="text/javascript"></script> <script type="text/javascript">bkLib.onDomLoaded(nicEditors.allTextAreas);</script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <LINK REL=StyleSheet HREF="inc/dyn_style.css" TYPE="text/css" MEDIA=screen /> <?php ?> <?php include('logic.inc'); mysqlConnect(); ?> <script type="text/javascript" src="bbeditor/ed.js"></script> <link rel="stylesheet" type="text/css" href="dyn_style.css" /> <title> Social </title> <script type="text/javascript"> function changeTitle(title) { document.title = title; } </script> </head> <body> <?php $inc = 'new_story.php'; $view = 'Newest '; $by = 'added'; $where = " "; $where2 = " "; $order = "ASC"; $gen = "All"; $rat = 'All'; $blerg = ""; $sort = 'newest'; //---------------------------------------------------------------------- if(isset($_GET['sub'])) { $sort = $_GET['sort']; switch($sort) { case "Most Popular"; $by = 'views'; $view = 'Most Popular '; $order = 'DESC'; break; case "Most Reviewed"; $view= 'Most Reviewed '; $by = 'reviews'; $order = 'DESC'; break; case "Newest"; $by = 'added'; $view = 'Newest'; $order = 'ASC'; break; } $genre = mysql_real_escape_string($_GET['cat']); $rating = mysql_real_escape_string($_GET['rat']); if($gen == 'All') { $where = " "; $blerg = ""; } else { $where = "WHERE cat='$gen'"; } if ($rat == "All") { $where2 = ' '; $blerg = 'AND'; } else { $where2 = $blerg ." rating = '$rat' "; } } //---------------------------------------------------------------------- ?> <?php serch(); ?> <form action="story.php" method="get"> <label id='inline'> Order By: </label> <select name='sort'> <option selected='yes' label='Currently Selected' > <?php echo $view; ?> </option> <option> Newest </option> <option> Most Popular</option> <option> Most Reviewed </option> </select> <input type='hidden' value='spec_view' name='p' /> <label id='inline'> Genre/Catagory: </label> <select name='gen'> <option selected='yes' label = 'Selected Genre - <?php echo $gen; ?>'> <?php echo $gen; ?> </option> <option> All </option> <option> Fantasy </option> <option> Adventure </option> <option> Science Fiction</option> <option> Drama</option> <option> Fable </option> <option> Horror</option> <option> Humor</option> <option> Realistic Fiction </option> <option> Tall Tale</option> <option> Mystery </option> <option> Mythology </option> <option> Poetry </option> <option> Shorty Story </option> <option> Romance </option> </select> <label id='inline'> Rating: </label> <select name='rat'> <option selected='yes' label = "Selected Genre - <?php echo $rat; ?>"> <?php echo $gen; ?> </option> <option> All </option> <option> C </option> <option> C13 </option> <option> YA </option> <option> A </option> </select> <input type='submit' value='Go!' name = 'go' /> </form> <?php $query = " SELECT * FROM story_info ORDER BY $by $order $where $where2 "; echo $query; $select = mysql_query($query) or die(mysql_error()); while($rows = mysql_fetch_assoc($select)) { $viewsdb = $rows['views']; $titledb = $rows['title']; $userdb = $rows['user']; $catdb = $rows['cat']; $ratdb = $rows['rating']; $id_db = $rows['story_id']; $sumdb = shorten($rows['sum']); echo "<h3><a href='?p=page&id=$id_db'> $titledb </a> </h3>"; echo "<div id='fun_info'>"; echo "$sumdb <br />"; echo "By <a href='?p=profile&user=$userdb'> $userdb </a> <br /> "; echo "$viewsdb Views | Rated: $ratdb | Catagory: <a href='?p=cat_view&gen=$catdb'> $catdb </a> </div>"; } ?> </div> </body> </html> Hey guys, Say I have two variables, the first being $level_1 = '100'; and $level_2 = '1000'; is there anyway to work out the difference between the two and output it? I have two textboxes that inputted time, and I want to get the difference between two time. for example: $IN = 13:35; $OUT = 17:35; $OTHours = ($OUT - $IN); $OTHours = 4.00; and it is correct, but I have a scenario like this: $IN = 21:35; $OUT = 05:35; $OTHours = -16.00; it should be 8.00. Any help is highly appreciated. Thank you.. if($_POST['Submit']=="Check"){ $issueid=(int)$_POST['issueid']; $returndate=mysql_real_escape_string($_POST['returndate']); list($temp)= mysql_fetch_row(mysql_query("select issueid FROM issue where issueid='$issueid'")); if($temp!=NULL){ $insert_query="insert into returning values($issueid,'$returndate')"; $result=mysql_query($insert_query,$linkID1); if($result){ list($bookid,$account,$issuedate,$duedate)= mysql_fetch_row(mysql_query("select bookid,account,issuedate,duedate FROM issue where issueid='$issueid'")); list($title,$name,$fine)= mysql_fetch_row(mysql_query("select title,name,fine FROM book,user where bookid='$bookid' AND acc='$account'")); $insert_book="update book set copies=copies+1 where bookid='$bookid'"; $result=mysql_query($update_book,$linkID1); Quote i want to take difference of $duedate and $returndate and store it to another variable checks that variable for number of days in it What is difference between php date(); and mysql NOW() and what should be used in script? In php date I have 13:00h, and from MySql have 17:00h. Current date on my comp is 19:00h. So time difference in php is +6 hours, and in mysql is +2 hours. How to synchronize this dates for users??? Hello. I have a simple enough code that takes information from one table and drops it into another. This is great, but I have 2a new complexities that I have been unable to code correctly. A. 'lastname', 'firstname' on table1 need to be combined into 'name' How & Where do I combine these strings and then pass them? Code: [Select] <?php include('dbconfig.php'); // Make a MySQL Connection mysql_connect("localhost", "$user", "$password") or die(mysql_error()); mysql_select_db("$database") or die(mysql_error()); $result = mysql_query( "INSERT INTO table2 (lastname, firstname, email) SELECT lastname, firstname, email FROM table1 WHERE email='someemail@gmail.com' ") or die(mysql_error()); ?> What I'm wanting to do is pass the $propertyid = "1"; threw my select value as you can see. so it passes variable so to speak.' Code: [Select] print "<td>Control:<form action=\"dashboard.php\" method=\"GET\" target=\"_self\"> <select name=\"control\" onChange=\"this.form.submit()\"> <option selected></option> <option>Take a Payment</option> <option>Print Ledger </option> <option>-----------------</option> <option value=\"comadd?propid=$propertyid\">Add Communication</option> <option>----------------</option> <option>Modify Property</option> <option>Remote Property</option> </select></td>"; Code: [Select] $query = "SELECT content FROM license WHERE serial = '".$serial."'"; Say I have two variables like below. I basically want to select one of them, but through random choice. How do I do that? $orange = 'Orange'; $apple = 'Apple';
Hi, all... have a basic question here as a newbie that I am... Many times, in some scripts I see (I think, if not mistaken), places where within a echo sentence I see variables echoed like this with no quotes: echo "First Name: $firstName and Last Name: $lastName"; as opposed to like one would expect: echo "First Name: " . $firstName . " and Last Name: " . $lastName"; Sometimes I see that also when variables are used in select/update statements I think.. like this: $query = mysql_query("SELECT FROM table WHERE column = $columnname"); Instead of like this within quotes: $query = mysql_query("SELECT FROM table WHERE column = '$columnname'"); Is it because some of these values may be numbers (numeric) as opposed to "strings"?? Not sure if I make sense... appreciate teh feedback, Thanks! Ok so I have this script which will take a propery formated .csv file and upload to my database. here's the part i need to know if it's possible. basically it puts a group of codes into the database but i need a field that will specify which user the code pertains to from another table. The most logical way to reference the user is with an id code; but short of having the user reference an id code to put in the csv file, is there anyway I can make a query that will INSERT INTO and upload the csv? here's my current code... <?php $tmpName = $_FILES['song_codes']['tmp_name']; include('database.php'); if($sc != ""){ mysql_query('LOAD DATA LOCAL INFILE \''.$tmpName.'\' INTO TABLE music_codes FIELDS ENCLOSED BY "\"" TERMINATED BY "," LINES TERMINATED BY "\n" ;') or die('Error loading data file.<br>' . mysql_error()); I have a table and the structure is Code: [Select] ID, UID, Site, Uname, PassI already have this <?php $result = mysql_query(sprintf("SELECT * FROM Logins WHERE UID = %d", $_COOKIE['UID_WatsonN'])); //check login table against cookie if(($num = mysql_num_rows($result)) > 0){ mysql_close(); ?> <table border="0" cellspacing="2" cellpadding="2"> <tr> <th>ID</th> <th>UID</th> <th>Site</th> <th>Uname</th> <th>Pass</th> </tr> <center> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"ID"); $f2=mysql_result($result,$i,"UID"); $f3=mysql_result($result,$i,"Site"); $f4=mysql_result($result,$i,"Uname"); $f5=mysql_result($result,$i,"Pass"); ?> <tr> <td><?php echo $f1; ?></td> <td><?php echo $f2; ?></td> <td><?php echo $f3; ?></td> <td><?php echo $f4; ?></td> <td><?php echo $f5; ?></td> </tr> <?php $i++; } } ?> which only gets the data from that one person. What is in the table is usernames and passwords for diffrent sites and i want to take all the usernames and passwords and put them into variables to pass on to the login form. Hi All! I've written up a script for my website. It\ is basically a virtual job quest. My queries are all correct it just isn't registering the variable for the session. It is $-SESSION[theid']. I want to be bale to use it in my table but I get an error. How do I write this in my SQL query for it to work. The page (when no errors), doesn't show my data. Here is my ocde: Code: [Select] <?php session_start(); include("config536.php"); ?> <html> <head> <link rel="stylesheet" type="text/css" href="style.css" /> </head> <?php if(!isset($_SESSION['username'])) { echo "<ubar><a href=login.php>Login</a> or <a href=register.php>Register</a></ubar><content><center><font size=6>Error!</font><br><br>You are not Logged In! Please <a href=login.php>Login</a> or <a href=register.php>Register</a> to Continue!</center></content><content><center><font size=6>Messages</font><br><br></center></content>"; } if(isset($_SESSION['username'])) { echo "<nav>$shownavbar</nav><ubar><img src=/images/layout/player.gif><a href=status.php>$showusername</a>.......................<img src=/images/layout/coin.gif> $scredits</ubar><content><center><font size=6>Basic Quests</font><br><br>"; $startjob = $_POST['submit']; $jobq = "SELECT * FROM jobs WHERE username='$showusername'"; $job = mysql_query($jobq); $jobnr = mysql_num_rows($job); if($jobnr == "0") { ?> <form action="<?php echo "$PHP_SELF"; ?>" method="POST"> <input type="submit" name="submit" value="Start Job"></form> <?php } if(isset($startjob)) { $initemidq = "SELECT * FROM items ORDER BY RAND() LIMIT 1"; $initemid = mysql_query($initemidq); while($ir = mysql_fetch_array($initemid)) { $ids = $ir['itemid']; } mysql_query("INSERT INTO jobs (username, item, time, completed) VALUES ('$showusername', '$ids', 'None', 'No')"); $wegq = "SELECT * FROM items WHERE itemid='$ids'"; $weg = mysql_query($wegq); while($wg = mysql_fetch_array($weg)) { $im = $wg['image']; $nm = $wg['name']; $id = $wg['itemid']; } $_SESSION['theid'] = $id; echo "<font color=green>Success! You have started this Job!</font><br><br>Please bring me this item: <b>$nm</b><br><br><img src=/images/items/$im><br><br><br>"; echo $_SESSION['theid']; } if($jobnr == "1") { $finish = $_POST['finish']; $okgq = "SELECT * FROM items WHERE itemid='$yes'"; $ok = mysql_query($okgq); while($ya = mysql_fetch_array($ok)) { $okname = $ya['name']; $okid = $ya['itemid']; $okimage = $ya['image']; } echo "Where is my <b>$okname</b>?<br><br><img src=/images/items/$okimage><br><br><br>"; echo $_SESSION['theid']; ?> <form action="<?php echo "$PHP_SELF"; ?>" method="POST"> <input type="submit" name="finish" value="I have the Item"></form> <?php } } if(isset($finish)) { $cinq = "SELECT * FROM uitems WHERE theitemid='$_SESSION[theid]'"; $cin = mysql_query($cinq); $connr = mysql_num_rows($cin); if($connr != "0") { echo "<font color=green>Success! You have the item.</font>"; } else { echo "<font color=red>Error! You do not have my item!</font>"; } } ?> . I basically just want to know how I can set this session as a variable. Also..I have a user login on every page and I want to be able to destroy JUST THE "theid" session and NOT the username session. How would I do that too? thanks for the help in advance! So, I'm working on a quiz system. The text and choices are stored in a MySQL database. I haven't gotten to the choices yet, I'm still having trouble with the text. Here's my code: Code: [Select] $querytext = "SELECT text FROM quiz id = '$prob'"; $result = mysql_query($querytext) or die(mysql_error()); echo $result; Yes, I'm already connected and stuff, that's just the snippet. I made sure that $prob is 1. Here's the MySQL Error I'm getting: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= ''' at line 1 I've looked all over the web and through the MySQL/PHP section of a 991-page PHP book. What am I doing wrong? I bet that I really, really failed epicly this time, but I never catch my epic fails and have to ask questions about them in order to fix the problem. So please reply Hi, I'm only just getting started with php and mysql but I like to go off the deep end. something is wrong with my script. it seems to work up untill it comes to the UPDATE part. It wont actually update the table and the mysql_affected_rows bit returns -1 I've searched for an explanation and can't find one. Can someone tell me what I'm doing wrong Thanks. //connect to mysql $con = mysql_connect("xxx","yyy","zzz"); if (!$con) { die('Could not connect: ' . mysql_error()); } //use the right database mysql_select_db("foo_bar"); //select all cancelled tickets $status_query = "SELECT * FROM jos_vm_orders where order_status = 'X'"; $status_result = mysql_query($status_query) or die(mysql_error()); while ($newArray = mysql_fetch_array($status_result, MYSQL_ASSOC)) { $order_id = $newArray['order_id']; $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity) WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') >= CURDATE()"); echo ("$product_quantity tickets released into $order_item_sku \n"); echo ("Affected rows "); echo (mysql_affected_rows()); echo ("\n"); } } mysql_free_result($status_result); mysql_free_result($quantity_result); mysql_close($con); ?> Hey Guys, I have to insert some data in MySQL but it wont work . Please have a look. <?php // to values are set to empty $vatsim=""; $ivao=""; // values from form in other page are set if(isset($_POST["pilotid"])) $pilotid=$_POST["pilotid"]; if(isset($_POST["network"])) $network=$_POST["network"]; if(isset($_POST["vid"])) $vid=$_POST["vid"]; if(isset($_POST["pilot"])) $pilot=$_POST["pilot"]; // if value is that copy data in this value, otherways in that value if ($network == "IVAO") { $ivao="$vid";} if ($network == "VATSIM") { $vatsim="$ivao";} // connect db include(dbconnect.inc.php); // first sql to update some data in one table $sql = "UPDATE `360283`.`jos_users` SET `IPS` = \'1\' WHERE `jos_users`.`id` = \'$pilotid\'"; $result1 = mysql_query($sql); // 2nd sql to insert some data in other table $sql2 = "INSERT INTO `360283`.`IPS_Pilots` (`ID`, `Name`, `Hours`, `Flights`, `LastFlight`, `IVAO`, `VATSIM`, `Enabled`, `Rating`) VALUES ('$pilotid', '$pilot', NULL, NULL, NULL, '$ivao', '$vatsim', '1', '0');"; $result2 = mysql_query($sql2); // sql to check if it was succesful $sql3 = "SELECT * FROM `IPS_Pilots` WHERE `ID` = '$pilotid' LIMIT 0, 30 "; $result3 = mysql_query($sql3); $num3 = mysql_numrows($result3); // echo succesfull or not if (!$num3) { echo "Sorry, but I failed to apply this pilot."; } else { echo "Pilot succesfully applied."; } ?> Thanks I am working on a simple web app in which I want to store about 20 variables in a MYSQL table so when I hand off the project the variables can be easily edited by the end user. Is it easy to store variables in a MYSQL table then query those and make them PHP variables? Really I just need to read a MYSQL table and pull two columns; AppOption and AppOptionValue then create PHP variables for each of the results making the AppOption the variable name and the AppOptionValue the value of said varible. Any Ideas? Thanks! Sean Hi Everyone, I'm trying to convert my MySql data into variables so I can use them on my website. For example, if I want to convert a city or even first or last name into a variable. How would I go about doing that? Thanks everyone! |
|||||||