|
PHP - Moved: Complex Select Statement
This topic has been moved to MySQL Help.
http://www.phpfreaks.com/forums/index.php?topic=327011.0 Similar TutorialsThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=322930.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=312377.0 Greetings - So I have an issue with my form right now which can be found he http://youawfulme.com/Form.html If you look, you will see two expanding columns under Providers and References. The HTML code for them to expand looks like this: Quote <span class="duplicateSpan"><a id="tfa_0583059728034-wfDL" class="duplicateLink" href="#">Next Provider</a></span> and the PHP code that handles their database storage looks like this: Quote // Insert Providers.. if(count($_POST['tfa_FirstName1']) > 1) { for($i = 0; $i < count($_POST['tfa_FirstName1']); $i++) { $tmpsql = "INSERT INTO tmpProviders (tmpId,FirstName,LastName,LicenseType,PrimarySpecialty,SecondarySpecialty,NYStateLicenseNum,DEANumber,NPINumber,EmailAddress,DaysPerWeek,PrimaryCareProv) VALUES ($oldid,'" . $_POST['tfa_FirstName1'][$i] . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_LastName1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_LicenseType1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_PrimarySpecialty1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_SecondarySpecial'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NYStateLicenseNu1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DEANumber1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NPINumber1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_EmailAddress1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DaysPerWeek1'][$i]) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d+/","\\1",$_POST['tfa_Ifthisindividual'][$i]) . "');"; mysql_query($tmpsql); } } else { $tmpsql = "INSERT INTO tmpProviders (tmpId,FirstName,LastName,LicenseType,PrimarySpecialty,SecondarySpecialty,NYStateLicenseNum,DEANumber,NPINumber,EmailAddress,DaysPerWeek,PrimaryCareProv) VALUES ($oldid,'" . $_POST['tfa_FirstName1'] . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_LastName1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_LicenseType1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_PrimarySpecialty1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d*/","\\1",$_POST['tfa_SecondarySpecial']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NYStateLicenseNu1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DEANumber1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_NPINumber1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_EmailAddress1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_/","",$_POST['tfa_DaysPerWeek1']) . "'"; $tmpsql = $tmpsql . ",'" . preg_replace("/tfa_([a-zA-Z]+)\d+/","\\1",$_POST['tfa_Ifthisindividual']) . "');"; mysql_query($tmpsql); } if(count($_POST['tfa_PracticeName']) > 1) { At this moment, when you fill out my form, all of the information saves to three separate tables. The Provider input goes into a temporary providers table, the Referrals to a temporary Referral , and the rest of the data to a temporary information table. Once the user submits the application, the temporary tables are cleared after having been moved to permanent tables (Still three tables) My goal is for all of these values to save to one table, whether it be from three separate temps to one final or one temp to one final. My programmer was having trouble figuring it out and kind of left me on my own.. I really need some help The Providers and Reference tables are done in such a way that if there are more than one entry, it will create a new row for each entry with a new ID number but the same "tempid" number, the latter matching the ID number in the main information table. Can I have it such that PHP will check to see if the tempIDs match, and if they do, move the information into new columns on the same row? So that way, instead of having say 3 different IDs with one temp ID for three different providers.. instead have that information all placed on one row, with one ID, and new columns? In my head, I figured I would expand my table 5x. If I have five columns now, I will have 25 after. For each "Provider Name", I will now have "Second Provider Name", "Third Provider" name, etc... If the user only adds one provider, these won't be used. If they had multiples, up to five, the info will be placed into these new columns. I just can't get this to work.. When I expand the column and inspect the element in HTML, it says, for example, Provider[1] and Provider[2] .. Incrementing in brackets for each addition. But if I try to, say Quote $SecondProviderName = $_POST['tfa_ProviderName[2]']; .. It simply doesn't work. Sorry for all this, I just need some help and so far you guys have been a great resource. Thank you I need some help with the query for a form that selects values across multiple columns, and allow users to select multiple values in several columns. http://brinsterinc.com/tpa/tpasearch.php I assume you build the where clause for the sql by determining if there is an option value has been selected in the single-value select boxes. But how do I handle the multi-select list boxes? I'm desperate for help! Need to get somewhere with this over the weekend! TIA! This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=306845.0 I have two tables...one is scheduled_umps and one is games. Both tables have a column 'game_id'. I need to select how many games a person is scheduled for in a given time frame. Here's what I have for the select...and it's not working (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 'WHERE scheduled_umps.ump_id = '34' AND games.game_id = scheduled_umps.game_id AN' at line 1) Code: [Select] <?php $today_is_this_date = date('Y-m-d'); $four_months_ago = date('Y-m-d', strtotime('-4 months', strtotime($today_is_this_date))); $four_months_from_now = date('Y-m-d', strtotime('+4 months', strtotime($today_is_this_date))); /// How many games have you worked? //$query = "SELECT COUNT(ump_id) FROM scheduled_umps WHERE `ump_id` = '$_SESSION[ump_id]'"; ///this one selects how many games you've worked NO MATTER WHAT THE DATE $query = "SELECT scheduled_umps.game_id, games.game_id, COUNT(scheduled_umps.ump_id) WHERE scheduled_umps.ump_id = '$_SESSION[ump_id]' AND games.game_id = scheduled_umps.game_id AND games.game_id BETWEEN $four_months_ago AND $four_months_from_now"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)){ echo "You have been scheduled on <b>". $row['COUNT(ump_id)'] ."</b> dates for the $_SESSION[association_name]."; } ?> [\code] Any ideas where I'm going wrong? Thanks for the help...I HATE multi-table selects! Hi All, I have the following Query: Code: [Select] $query = 'SELECT `BookingID`,`OfferID`, `name`, `OfferCount`,`OfferPrice` FROM `Offers_Finance` JOIN offers_details ON `OfferID` = `o_d_id` WHERE `BookingID` ="' .$bookerid. '"'; if ( !$result = $mysqli->query( $query ) ) { die( $mysqli->error ); } $field = $result->fetch_object(); This will return anywhere between 0 and 50 Rows of Data. For Each Row returned, I want to do the following: Quote echo '<tr>'; echo '<td>' . $field->name.'</td>'; echo '<td></td>'; echo '<td>£' . $field->OfferPrice.'</td>'; echo '<td></td>'; echo '<td>' . $field->OfferCount . '</td>'; echo '<td></td>'; echo '<td>£' . $field->OfferPrice * $field->OfferCount . '</td>'; echo '</td>'; echo '</tr>'; The problem I have is - how do I loop through the results returned from the query to output multiple rows? Sorry if this doesn't make much snese, I'm new to PHP and the whole web development world, but have been landed with finishing a project someone else started! If someone would be kind enough to turn this into a working example, it would help no end as I have about 20 of these things to figure out across the project! $sql=mysql_query("SELECT * FROM `buds` WHERE `level`<='$user_level' UNION SELECT * FROM `buds`, `unlocked_buds` WHERE buds.`id` = unlocked_buds.`bud_id` ORDER BY buds.`id` ASC") or die("A MySQL error has occurred.<br />Your Query: " . $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error()); I have been trying to learn about UNION select statements. I ran the query above and got this response: Quote Error: (1222) The used SELECT statements have a different number of columns I think I know what the problem is, but not sure how to fix it. There is two columns, one is "buds" which holds the flowers seeds info. The seconds is "unlocked_buds" which just links the "id" from buds to "user_id" to the user table. Both buds and unlocked_buds are both 8 columns. What do I need to learn? I have a Select statement that relies on two session variables to complete the statement. One variable, $egroup, supplies a column name. I cannot make the SELECT work. It works fine if I substitute the real name of one of the columns such as "egroup1", or "egroup6", but that's what the variable $egroup is for. Can anyone tell me where I'm wrong? I guess the answer is in the syntax, but I'm not very good at this and I've been on it for too long. Any help gratefully taken. Code: [Select] <?php $query1 = mysql_query("SELECT * FROM topics WHERE managerId='".$managerId."' AND "$egroup"= 1 ORDER BY title ASC"); ?> Hey guys, I'm trying to get this working... No errors right now, but I'm not returning any results :/ been messing with it for days. Code: [Select] <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" /> <label for="search_by">Search By</label> <select name="search_by"> <option value"player">Player</option> <option value"city">City</option> <option value"alliance">Alliance</option> <option value"browse">Browse</option> </select> <input type="text" name"search"> <input type="submit" value="search" name="search"> <?php $search_by = $_POST['search_by']; $search = $_POST['search']; echo "<table><tr><td>Player</td><td>city</td><td>alliance</td><td>x</td><td>y</td><td>other</td><td>porters</td><td>conscripts</td><td>Spies</td><td>HBD</td><td>Minos</td><td>LBM</td><td>SSD</td><td>BD</td><td>AT</td><td>Giants</td><td>Mirrors</td><td>Fangs</td><td>ogres</td><td>banshee</td></tr>" ; $dbc = mysqli_connect('xx', 'xx', 'xx', 'xx') or die ('Error connecting to MySQL server'); $sql = "SELECT * FROM players WHERE ('$search_by') LIKE ('$search') "; //problem is here^^?? $result = mysqli_query($dbc,$sql) or die("Error: " .mysqli_error($dbc)); Not sure, any help would be greatly appreciated. Hi everybody i am beginnet with working with sql in php..so i confused.....i made one scripte and i am making the secound and now i am facing a problem....i hope u could help!! i have a dat=abse of my chatroom....this is the code i typed...please have a look at it! Code: [Select] $usern=$_SESSION['etchat_'.$this->_prefix.'username']; $con1 = mysql_connect("localhost","manosir_main","********"); if (!$con1) { die('Could not connect: ' . mysql_error()); } mysql_select_db("manosir_manoshos_main", $con1); $users= mysql_query(" select etchat_user_online_user_sex from db1_etchat_useronline where etchat_user_online_user_name = $usern "); in my database, i ahve a table called db1_etchat_useronline that keeps record of online users in chatroom. as u usee i qant to to get the user sex ( etchat_user_online_user_sex) of th current user!!! as u see i conected to database correctly but now i dont know how to echo the $users . before this i echo my sql results with mysql_fetch_array ..but now i cant.......i got nothing!!!! can someone help me!!!!! What's the correct syntax for having a variable in a select statement? Here's an example of what I'm trying to do (after I'm already connected to the database). Code: [Select] $username = "thomas"; $query = mysql_query( "SELECT * from users WHERE username = $thomas" ); if( mysql_num_rows( $query ) > 0 ) { bla bla... It works if I don't put the WHERE part in, but I get an error if I use it, so I'm assuming I have the wrong syntax for using a variable in the select statement. here is some simple code for getting and displaying fata from a database Code: [Select] $sql="SELECT * FROM messages WHERE m_id = '".$id."'"; $result = mysql_query($sql); <table border='0' cellspacing="4"> while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<th>Message</th>"; echo "</tr>"; echo "<tr>"; echo "<td>" . $row['message'] . "</td>"; echo "</tr>"; } now ive used that while loop to display those results how can i do that again without having a new SQL statement. i cant do 2 while loops because the first one has already got to the end of the amount of rows basically how can i have another while loop displaying those same results again?
//DATABASE CONNECTION VARIABLES
$myserver ="localhost";
$myname = "myname";
$mypassword = "mypassword";
$mydb ="mygamedb";
/*SQL CONNECTION*/
// Create connection
$conn = new mysqli($myserver, $myname, $mypassword, $mydb);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
else {
//variables
$username = $_POST["username"];
$password = $_POST["password"];
$ip = $_SERVER['REMOTE_ADDR'];
//INSERT USER
//prepare and bind
$stmt = $conn->prepare("INSERT INTO Players (Username, Password, IP) VALUES (?, ?, ?)");
//bind parameters
$stmt->bind_param("sss", $username, $password, $ip);
//set parameters and execute
$stmt->execute();
//close
$stmt->close();
//FETCH ID
$resultnews = mysql_query("SELECT * FROM Players WHERE Username ='$username'");
$rownews = mysql_fetch_array($resultnews);
$user_id = $rownews["ID"];
}
After having suffered an SQL injection into one of my sites, I figured it was time to overhaul it and use prepared statements. I am new to this. I figured out how to an INSERT with an example, but now I need to fetch an ID and cannot get it to work. Any help much obliged. All I need is just one good example. Looked all over the place, but all I get are insert examples, which is NOT what i need. Really need one with a select and fetch.
I need to convert the following select statement to a pdo->query but have no idea how to get it working: SELECT t.id FROM ( SELECT g.* FROM location AS g WHERE g.start <= 16785408 ORDER BY g.start DESC, g.end DESC LIMIT 1 ) AS t WHERE t.end >= 16785408; Here's the code I'm trying:
<?php
$php_scripts = '../../php/';
require $php_scripts . 'PDO_Connection_Select.php';
require $php_scripts . 'GetUserIpAddr.php';
function mydloader($l_filename=NULL)
{
$ip = GetUserIpAddr();
if (!$pdo = PDOConnect("foxclone_data"))
{
exit;
}
if( isset( $l_filename ) ) {
$ext = pathinfo($l_filename, PATHINFO_EXTENSION);
$stmt = $pdo->prepare("INSERT INTO download (address, filename,ip_address) VALUES (?, ?, inet_aton('$ip'))");
$stmt->execute([$ip, $ext]) ;
$test = $pdo->prepare("SELECT t.id FROM ( SELECT g.id FROM lookup AS g WHERE g.start <= inet_aton($ip) ORDER BY g.start DESC, g.end DESC ) AS t WHERE t.end >=inet_aton($ip)");
$test ->execute() ;
$ref = $test->fetchColumn();
$ref = intval($ref);
$stmt = $pdo->prepare("UPDATE download SET ref = '$ref' WHERE address = '$ip'");
$stmt->execute() ;
header('Content-Type: octet-stream');
header("Content-Disposition: attachment; filename={$l_filename}");
header('Pragma: no-cache');
header('Expires: 0');
readfile($l_filename);
}
else {
echo "isset failed";
}
}
mydloader($_GET["f"]);
exit;
It gives the following error: QuoteFatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.144.181) ORDER BY g.start DESC, g.end DESC ) AS t WHERE t.end >=inet_aton(7' at line 1 in /home/foxclone/test.foxclone.com/download/mydloader.php:19 Stack trace: #0 /home/foxclone/test.foxclone.com/download/mydloader.php(19): PDO->prepare('SELECT t.id FRO...') #1 /home/foxclone/test.foxclone.com/download/mydloader.php(38): mydloader('foxclone40a_amd...') #2 {main} thrown in /home/foxclone/test.foxclone.com/download/mydloader.php on line 19 How do I fix this? Hi Guys I don't know if this is possible but can someone point me in the right direction. I have a php function which takes two inputs and returns an output. for simplicity's sake let's say it's an addition function. What I want to do is use a mysql select statement to show all the rows from a database where field1 and field2 equal '3'. Here's the sort of thing I mean. function addNumbers($one,$two) { return $one + $two; } mysql_query("SELECT * FROM table WHERE 'addNumbers(field1,field2)' = '3'"); What I actually want to do is a lot more complex than this but I am trying to understand how to make the syntax work in simple terms first. Can anybody help? Many Thanks Dan I have got a set of data for a country for each week of the year, but want to display the data a quarter at a time, using a form which posts a value into LIMIT, which was OK for the 1st quarter, but for subsequent quarters I needed to use LIMIT in the form LIMIT start, rows.
I found by experiment that this had to be the last statement in the query.
when I added the start I got an error:
Parse error: syntax error, unexpected ',' in /homepages/43/d344817611/htdocs/Admin/Visitdata.php on line 10
Here is the SQL part of my code:
$Visit_data="SELECT WeekNo.WNo, WeekNo.WCom, Countries.Country, ctryvisits.CVisits FROM ctryvisits
LEFT JOIN Countries
ON ctryvisits.country=Countries.CID
LEFT JOIN WeekNo
ON ctryvisits.WNo=WeekNo.WNo
WHERE Countries.Country = '".$_POST["Country"]."'
ORDER BY ctryvisits.WNo LIMIT '".$_POST["QUARTER"]."'" - 13, '".$_POST["QUARTER"]."'";
Is this the best way of doing what I require or is there an easier way of achieving what I want?
how do i handle single quotes in sql query Code: [Select] " SELECT name from phrase WHERE name='$stitle' ";this returns an error because the name contains single quotes like this: Johnson's. Is it possible to have a loop inside a mysql select statement? If yes, can someone please give me an idea. I'm able to use PHP-ODBC to read the tables in an MDB file, as well as the column names on the tables. But when I try to read data from a particular table, it fails (I don't get an error, instead the browser pops up a message asking if I want to save the file "mdbtest.php" which is 0 bytes in size)! Any idea what's wrong? Code: [Select] //THIS WORKS $conn = odbc_connect( 'TestDB', '', '' ); if ( !$conn) exit("Connection Failed: " . $conn); //STILL FINE HERE $sql = 'SELECT * FROM Customers'; //INCLUDING THIS LINE OF CODE CAUSES THE PROBLEM $rs = odbc_exec( $conn, $sql ); |
|||||||