Jump to content

Search the Community

Showing results for tags 'sql'.

  • 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 (PhpStorm, VS Code, 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

  1. ok i created this to add a user to follow list if(Params::getParam('follow')!='') { if(osc_logged_user_id()!='') { $fav = $conn->osc_dbFetchResult("SELECT * FROM %st_follow WHERE fk_i_seller_id = %d AND fk_i_user_id = %d", DB_TABLE_PREFIX, Params::getParam('follow'), osc_logged_user_id()); if(!isset($fav['fk_i_user_id'])) { $conn->osc_dbExec("INSERT INTO %st_follow (`fk_i_user_id`, `fk_i_seller_id`) VALUES (%d, %d)", DB_TABLE_PREFIX, osc_logged_user_id(), Params::getParam('follow')); } } } now i need something to remove user from same list, im new to php and mysql so i tried something like this but it doesnt work if(Params::getParam('unfollow')!='') { if(osc_logged_user_id()!='') { if(!isset($fav['fk_i_user_id'])) { $conn->osc_dbExec("DELETE FROM %st_follow fk_i_user_id = %d AND fk_i_seller_id = %d LIMIT 1", DB_TABLE_PREFIX, osc_logged_user_id(), Params::getParam('unfollow')); } } } can someone please help me make it work, thank you so much.
  2. Hi all, Okay, I'll try to explain briefly but thoroughly on what I'm trying to do. FYI I have a general working knowledge of PHP & mySQL, and use Dreamweaver and it's built-in tools regularly. I have a registeration form, which gives the option to register into 4 age groups (via a radio button), which just submits as text into the database. What I am looking to be able to do is "Show If" (I think?) a disabled="true" to disable a radio button, once a certain amount of records have been submitted for a specific group. So I'm assumming the way to do this is to call up the full list from the database (not very many entries) and check if the limit of 15 per age group is reached? Let me know if that makes sense, any if you can give me any guidance. It will be much appreciated! Also, if you have the time to explain your coding to me too, so I can learn from it, that would be great! --EVP
  3. <?php //session status block include "connect_to_mysql.php"; ?> <?php //script error reporting //error reporting error_reporting(E_ALL); ini_set('display_errors','1'); ?> <?php //parse form data to database //parse the form data and add inverntory items to the system //CHANGE ALL VALUES TO SUIT NEEDS OF CURRENT DATABASE //FORM DATA AND ADD BOOK TO THE SYSTEM DATABASE if (isset($_POST['BOOKNAME'])) { $BOOKNAME = mysql_real_escape_string($_POST['BOOKNAME']); $BOOKPRICE = mysql_real_escape_string($_POST['BOOKPRICE']); $CATEGORY = mysql_real_escape_string($_POST['CATEGORY']); $BOOKDESCRIPTION = mysql_real_escape_string($_POST['BOOKDESCRIPTION']); // See if that product name is an identical match to another product in the system $sql = mysql_query("SELECT ID FROM BOOK WHERE BOOKNAME='$BOOKNAME' LIMIT 1"); // Add this product into the database now $sql = mysql_query("INSERT INTO BOOK (BOOKNAME, BOOKPRICE, BOOKDESCRIPTION, CATEGORY, DATE) VALUES('$BOOKNAME','$BOOKPRICE','$BOOKDESCRIPTION','$CATEGORY',now())") or die (mysql_error()); $pid = mysql_insert_id(); // Place image in the folder $newname = "$pid.jpg"; move_uploaded_file( $_FILES['fileField']['tmp_name'], "inventory_images/$newname"); echo 'Book Successfully Uploaded <a href="index.php"><b>Click Here To Go To Home Page</b></a>'; exit(); } ?> <?php //this block grab the whole list for viewing //THIS BLOCKS GRABS THE WHOLE LIST FOR VIEWING $product_list = ""; $sql = mysql_query("SELECT * FROM BOOK ORDER BY date_added DESC"); $productCount = mysql_num_rows($sql); // count the output amount if ($productCount > 0) { while($row = mysql_fetch_array($sql)){ $ID = $row["ID"]; $BOOKNAME = $row["BOOKNAME"]; $CATEGORY = $row["CATEGORY"]; $BOOKDESCRIPTION = $row["BOOKDESCRIPTION"]; $BOOKPRICE = $row["BOOKPRICE"]; $product_list = "Product ID: $ID - <strong>$BOOKNAME</strong> - $$BOOKPRICE - <em>Added $date_added</em> • <br />"; $date_added = strftime("%b %d, %Y", strtotime($row["date_added"])); } } else { $product_list = "There are currently no books for sale, check back soon !"; } ?> <!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>NTU Book Browser</title> <link rel="stylesheet" href="style.css" /> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script> <script> $(function() { $("#text-one").change(function() { $("#text-two").load("textdata/" + $(this).val() + ".txt"); }); }); </script> </head> <body> <div id="wrapper"> <?php include_once("template_header.php");?> <div id="wrapper_content"> <div id="headers"> <h2> Books </h2> </div> <div id="main"> <div id="add_item"> <p><i> <a href="books.php#bookForm">+Sell Book</a></i></p> </div> <?php echo $product_list; ?> <a name="bookForm" id="bookForm"></a> <h3> Add New Book Form </h3> <form action="books.php" enctype="multipart/form-data" name="myForm" id="myform" method="post"> <table width="90%" border="0" cellspacing="0" cellpadding="6"> <td align="right">Book Name</td> <td><select name="CATEGORY" id="text-one"> <option selected value="base">Select Book Category</option> <option value="biomedicalscience">Biomedical Science</option> <option value="history">History</option> <option value="spanish">Spanish</option> <option value="economics">Economics</option> <option value="sportsscience">Sports Science</option> <option value="forensicsscience">Forensics Science</option> <option value="computersystems">Computer Systems</option> <option value="english">English</option> <option value="psychologyandeducation">Psychology and Education</option> </select> </td> <br /> <td align="right"></td> <td> <select name="BOOKNAME" id="text-two"> <option>Select Book</option> </select> </td> </tr> <tr> <td align="right">Book Price</td> <td><label> £ <input name="BOOKPRICE" type="text" id="BOOKPRICE" size="12" /> </label></td> </tr> <tr> <tr> <tr> <td align="right">Book Description</td> <td><label> <textarea name="BOOKDESCRIPTION" id="description" cols="64" rows="5"></textarea> </label></td> </tr> <tr> <td align="right">Book Image</td> <td><label> <input type="file" name="fileField" id="fileField" /> </label></td> </tr> <tr> <td> </td> <td><label> <input type="submit" name="button" id="button" value="Add Book" /> </label></td> </tr> </table> </form> </div> <div id="footer"> <?php include_once("template_footer.php");?> </div> </div> </div> <div class="clear"></div> </body> </html>
  4. I have the query below which works great. Alls I want to do is all the projects from the query grouped by the description. $query_rs_installs = "SELECT Calls.Call_Ref, Calls.Link_to_Contract_Header, Calls.Order_No, Calls.Date_Received, Calls.Scheduled_Date_Time, Clients.Co_Name, Clients.Post_Code, LU_Call_Types.Call_Type_Description, LU_Call_Types.Type_Band, LU_Call_Status.Call_Status_Description, LU_Company_Types.Company_Type_DescriptionFROM { oj (((Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.LU_Call_Types LU_Call_Types ON Calls.Call_Type = LU_Call_Types.Call_Type_Code) INNER JOIN Siclops_Dispense.dbo.LU_Call_Status LU_Call_Status ON Calls.Last_Event_Status = LU_Call_Status.Call_Status_Code) INNER JOIN Siclops_Dispense.dbo.Clients Clients ON Calls.Link_to_Client = Clients.Client_Ref) LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Company_Types LU_Company_Types ON Clients.Company_Type = LU_Company_Types.Company_Type_Code}WHERE Calls.Link_to_Contract_Header = '".$row_rs_member['companyident']."' AND (LU_Call_Types.Type_Band = 'Project' OR LU_Call_Types.Type_Band = 'Project Complete' OR LU_Call_Types.Type_Band = 'Project Invoiced') AND (LU_Call_Status.Call_Status_Description = 'Reported Done' OR LU_Call_Status.Call_Status_Description = 'PTF Rep Done' OR LU_Call_Status.Call_Status_Description = 'Proforma Sent' OR LU_Call_Status.Call_Status_Description = 'Paperwork Recvd' OR LU_Call_Status.Call_Status_Description = 'In Query' OR LU_Call_Status.Call_Status_Description = 'Cryo PW Sent' OR LU_Call_Status.Call_Status_Description = 'Complete' OR LU_Call_Status.Call_Status_Description = 'Awaiting P/work' OR LU_Call_Status.Call_Status_Description = 'Awaiting Invoic' OR LU_Call_Status.Call_Status_Description = 'Await TB Return' OR LU_Call_Status.Call_Status_Description = 'ApplicationSent')GROUP BY LU_Call_Status.Call_Status_Description"; Now before the group by the query works fine. As soon as i write it in the query fails completely. I have also tried selecting distinct and unique on the same field but each time I write them in it fails. I believe this maybe because this is an MS SQL query and as such I may have the syntax wrong. Any ideas? Thanks guys
  5. Hi, I'm just wondering but how would I go about making a query so that I can fetch two valid pieces of information and display them? So In this case I would like to query my database to find the correct Firstname and Lastname of that use through their session? Here's something I made, it works! But it only fetches the first result and doesn't return the correct name for the correct user. <?php if ($stmt = $mysqli->prepare("SELECT id, firstname, lastname FROM users WHERE id LIMIT 1")) { $stmt->execute(); $stmt->store_result(); $stmt->bind_result($user_id, $firstname, $lastname); $stmt->fetch(); if($stmt->num_rows == 1) { $_SESSION['user_id'] = $user_id; $_SESSION['firstname'] = $firstname; $_SESSION['lastname'] = $lastname; echo $firstname; echo ' '; echo $lastname; } } ?> So at this point it's displaying two names but they are the first record in my database and it's not displaying the correct name for the correct user so for example.: I'm user 6 in the database with the name John Doe but it's displaying record 1 in the database which is Luke Void, basically I want it so when John Doe logs into his account, it says on the screen 'John Doe', so it matches the correct client. Help on this one please, I'm kind of newish to databases... thanks
  6. Im developing a site a that has login function, i have about 5 users with different user id. The problem is the code im using logs all the users as the first user in my database table, this means im essentially logged in as a another user. // function that gets users id from table users function user_id_from_username( $username ){ $username = sanitize( $username ); // do the query first $result = mysql_query( "SELECT user_id FROM users WHERE username = '$username'" )or die("Could not perform select query - " . mysql_error());;;; // if there was an error, or no results, $result will be FALSE. // if $result is not false, then you know there was a row returned $num_rows = mysql_num_rows($result); if($num_rows == 1) { return true; } else { return false; } } // function that checks the credentials and should return the correct user_id. function login($username, $password) { $user_id = user_id_from_username($username); $username = sanitize($username); $password = ($password); $query = mysql_query("SELECT COUNT(`user_id`) FROM guys WHERE username='$username' AND password='$password'"); return (mysql_result($query, 0) == 1) ? $user_id : false; } // finally logging the user and redirecting $login = login($username, $password); if ($login == false) { $errors[] = 'This username and password combination is incorrect'; }else { $_SESSION ['user_id'] = $login; header ('Location: index.php'); exit(); } function logged_in() { return (isset($_SESSION['user_id'])) ? true : false; } How can i log in the others users with the correct user id, and not just the first user ?
  7. I have a variable called $sqlCommand , variable is listed twice with two different values. the first $sqlCommand is holding a value of creating a table called page the second $sqlCommand is holding a value of create a table called blog As you can see in the code below : // Create the table 1 for storing pages $sqlCommand = "CREATE TABLE pages ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, page_title VARCHAR(255), page_body TEXT, page_views INT NOT NULL default '0', FULLTEXT (page_title,page_body) ) ENGINE=MyISAM"; $query = mysql_query($sqlCommand) or die(mysql_error()); echo "<h3>Success creating Pages table</h3>"; // Create the table 2 for storing Blog entries $sqlCommand = "CREATE TABLE blog ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, blog_title VARCHAR(255), blog_body TEXT, blog_views INT NOT NULL default '0', FULLTEXT (blog_title,blog_body) ) ENGINE=MyISAM"; $query = mysql_query($sqlCommand) or die(mysql_error()); echo "<h3>Success creating Blog table</h3>"; My question is how is php reading this as two separate values ?? they both seem to be declared in a global fashion , yet when they are inputted into the $query function they both are produced separately ? anybody know the reason for this ? thanks.
  8. Hi, I'm just wondering but if my webhost has SQL version 5.1, what are the syntax of that for PHP login & register forms? So like $q = "INSERT INTO `Table1` (`username`,`password`,`email`) " ."VALUES ('".$_POST["username"]."', " ."PASSWORD('".$_POST["password"]."'), " ."'".$_POST["email"]."')"; Would this be the right use of syntax? I'm having a few problems with making a clean and safe php login and register form. Thanks.
  9. suppose i've one starting data(1/12/2013) and final date (26/12/2013). What i want is that to update the database with the difference between the dates every day... means start data | Final Data | Days Left 1/12/2012 | 26/12/2012 | 25 <-------------- automatically done every day and updated in the database over writing the original values Means after 2 days it will show start data | Final Data | Days Left 1/12/2012 | 26/12/2012 | 23 Any help will be greately appreciated.....
  10. im trying to make a script where the user can update there details, (in this case the email) but when the script is running it comes up sucess to say its worked and updated. however when i open up my database in notepad or sql workbench the email has not changed its still the same. any ideas what it could be? or maybe im reading my database info wrong? CODE: <?php session_start(); $user_name = "root"; $password = ""; $database = "fixandrun"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); $currentemail = $_POST['currentemail']; $newemail = $_POST['newemail']; $confirmnew = $_POST['confirmemail']; $user = $_SESSION["username"]; if ($db_found) { $dbemail =mysql_query("SELECT email FROM staff WHERE username= '$user'"); //echo "$currentemail"; //echo "$newemail"; //echo "$confirmnew"; //echo "$dbemail"; if($currentemail == $dbemail || $newemail == $confirmnew) { $query = "UPDATE staff SET email = '$confirmnew' WHERE username = '$user'"; if(mysql_query($query)){ echo "updated";} else{ echo "fail";} } else { echo" some of your details are incorrect please try again. check that your current email is entered correctly and the new email matches."; } } else { print "Database NOT Found."; mysql_close($db_handle); } ?>
  11. Hi. im new to the site and i need a bit of help. im coding a website for my coursework and ive created a log in script. the script works but once the user has logged in i want to get a message saying welcome "firstname" instead of welcome "username" i have managed to get it to say welcome and there username but its not very formal i would like there name they use when they register. here is the code ive got so far. any help would be greatly appriciated. <?php session_start(); $user_name = "root"; $password = ""; $database = "fixandrun"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); $username = $_POST["username"]; $password = $_POST["password"]; echo"$password"; echo"$username"; if ($db_found) { $result =mysql_query("SELECT 1 FROM staff WHERE username= '$username' and password= '$password'"); if ($result && mysql_num_rows($result) == 1) { $sqlq = mysql_query("SELECT firstname FROM staff WHERE username = '$username'"); $_SESSION['name']=$sqlq; session_register("username"); header("location:homelogged.php"); } else { Echo "Username or Password is incorrect. Click <a href='home.php'>Here</a> to be taken back to the home page."; } } else { print "Database NOT Found."; mysql_close($db_handle); } ?> and on my home page i have : <?php echo "Welcome " . $_SESSION["name"] . "<br>"; echo "last logged in at " . date("l, F d, h:i" ,time())."<br>"; ?> this works but i get welcome and 0 instead of welcome and a name
  12. Hey, Im new to PHP and am trying to make a simple form where it puts the form data into a table called "users" This is the form: <p><form method="post" action="register.php"> <table border="0" align="center"> <tr> <td>Username</td><td><input type="text" name="username" size="15" > </tr> <br /> <tr> <td>Password</td><td><input name="password" type="password" size="15"></td> </tr> <br /> <td><input type="submit" value="Sign Up"/></td><td></td> </table> This is the php code: <?php $dbhost = ''; $dbname = ''; $dbuser = ''; $dbpass = ''; mysql_connect($dbhost,$dbuser,$dbpass); mysql_select_db($dbname); $order = "INSERT INTO users (username, password) VALUES ('$username', '$password')"; $result = mysql_query($order); if($result){ echo("<br>It Worked!"); } else{ echo("<br>It Failed!"); } ?> But when i upload it to the server and try it, it says there was a entry but its blank..... When i upload it to a server and try it, it automaticly seems to post the data to the database before i even enter a username/password! when i type a username/password in and post it again it says there was another entry but its blank..... any help? - Thanks!
  13. This is mysql code im trying to run: SELECT i.id, i.courseid, i.title, i.info, i.lasteditedby, u.id, u.forename, u.surname FROM courseinformation as i JOIN users AS u ON (i.lasteditedby = u.id) WHERE i.courseid = :courseid ORDER BY i.id desc LIMIT 2; Im getting this error : /* SQL Error (1064): 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 ':courseid ORDER BY i.id desc LIMIT 2' at line 1 */ My aim is to get id, courseid, title, info, lasteditedby from CourseInformation table, and then Id, forename and surname from user table. Where the userid is the same as lasteditedby. I really can't see what sql syntax is wrong as i've used :courseid in other pdo sql querys that ive run for reference, this is my php code with that sql in $courseid = 'G11111'; $sql = "SELECT i.id, i.courseid, i.title, i.info, i.lasteditedby, u.id, u.forename, u.surname FROM courseinformation as i JOIN users AS u ON (i.lasteditedby = u.id) WHERE i.courseid = :courseid ORDER BY i.id desc LIMIT 2"; $sql->bindParam(":courseid", $courseid); $sql->execute(); foreach ($db->query($sql) as $row) { echo '<div class="announceTitle">'; echo $row['title'].'<br />'; echo $row['forename'].' '.$row['surname'].'<br />'; echo '</div> <div class="announceText">'; echo $row['info']; echo '</div> <br /> <br />'; } Could anyone please point me in the direction as to what is wrong? Thanks for reading
  14. [edit] i believe ive posted this in the wrong section D: please could a mod delete or move it! thanks! ----------------------- Hi Im trying to get : - title, newstext, authorId from indexNews - id, forename, surname from users where authorId is id im using pdo, which is where im stuck here is what I've done so far : try { $sql = "SELECT i.title, i.newstext, i.authorId, u.id, u.forename, u.surname FROM indexNews as i JOIN users AS u ON (i.authorId = u.id)"; //$sql = "SELECT * FROM indexNews"; foreach ($db->query($sql) as $row) { echo '<div class="announceTitle">'; echo $row['i.title'] .' - '. $row['i.authorId'] . '<br />'; echo $row['u.surname'].' '.$row['u.forename'].'<br />'; echo '</div> <div class="announceText">'; echo $row['i.newstext']; echo '</div> <br /> <br />'; } } catch(PDOException $e) { echo $e->getMessage(); } when i run this i get : line 100 is echo $row['i.title'] .' - '. $row['i.authorId'] . '<br />'; i think that the issue might be with the array? but i'm not sure. I've tried googling and have been looking for a tutorial, but haven't found an answer. could anyone give me some hints as to where i'm going wrong? Thanks for your time
  15. Im trying to create a repeated list of all the years forms have been filled in, in my database. The database has been running for 4 years and the row creation_date is unixtime. I want a list that looks as follows 2010 2011 2012 2013 to create this list I understand Im after creation_date between the highest and lowest value of Year in unixtime. So I wrote the following query mysql_select_db($database_dbconnet, $dbconnet); $query_rs_frmyear = sprintf("SELECT creation_date FROM customer_details WHEREcreation BETWEEEN (SELECT MIN(DATE_FORMAT(FROM_UNIXTIME(creation_date), 'Y')) AS lowyear FROM customer_details)AND (SELECT MAX(DATE_FORMAT(FROM_UNIXTIME(creation_date), 'Y')) AS highyear FROM customer_details)"); $rs_frmyear = mysql_query($query_rs_frmyear, $dbconnet) or die(mysql_error()); //echo the yearsdo {echo $row_rs_frmyear['creation_date']; } while($row_rs_frmyear = mysql_fetch_assoc($rs_frmyear)); Which fell flat on its face!!! Any ideas anyone?
  16. I've create a table of "followers" where users can follow other users. It's set up as such: ID username followname username is the name of the person that is doing the action/following and followname is the person who is being followed. I want to set up a news feed for each user on the profile.php page. The news feed should look at who the user is following (followname) and then fill the news feed with all the posts from those people in order of what time they were posted. The posts are in a different table called "needs" and each post is timestamped. Can anyone help me in started to code this?
  17. Schweppers

    SQL

    Hi, I'm trying to make a forum for my website and I keep getting this error when trying to create a new topic Error: 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 10 I don't know what page this could be referring to. I have 2 pages to connect to two different DB's one being: config.inc.php the other being: connect.php I don't know what all more information you need to help with this, comment and I'll tell you more if needed.
  18. I have a search box which uses multiple query depending on the search criteria the following queries work just fine select * from `car_post` where `approve` = '1' and `caru_city` = '2' select * from `car_post` where `approve` = '1' and `caru_make` = '7' and `caru_city` = '2' These queries mentioned above work just fine.. but i want the results to show the latest entries in the table so write the query as select * from `car_post` where `approve` = '1' and `caru_make` = '7' and `caru_city` = '2' ORDER BY caru_id DESC This query works perfectly fine in the SQL client.. it gives the results there... but as i am using it on my page.. by just adding ORDER BY caru_id DESC The query gives the following error 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 'DESCLIMIT 0, 10' at line 1 I dont understand if the query is working all fine in the sql client directly why isnt it working here?
  19. I am trying to make a query for site searching. What I need to do with this query is I want to select four main columns according to search keyword (tutors, institutes, subjects, cities). when typing keyword the query must check this four tables and need to display search result under this four categories. when displaying search result I need to add some values to make meaningful search result. Eg: Think displaying tutors under tutors category as a search result I need to display his/her name, city, profile image etc. Its something like this. Tutors tutor's name city profile image institutes institute's name city profile image subjects subject name category name which belong this subject cities city name district name which city belong etc... I tried it something like this SELECT keyword, col, city_name, image_name, tutor_code FROM ( SELECT tutor_name AS keyword, 'Tutors' AS col, IFNULL(c1.city_name, '') city_name, IFNULL(ti.image_name, '') image_name, tutor_code FROM tutors AS t LEFT JOIN address a ON t.address_id = a.address_id LEFT JOIN city c1 ON a.city_id = c1.city_id LEFT JOIN tutor_images ti ON t.tutor_id = ti.tutor_id AND ti.image_type = 'profile' UNION SELECT subject_name AS keyword, 'Subject' AS col, '' city_name, '' image_name, '' tutor_code FROM subjects UNION SELECT city_name AS keyword, 'City' AS col, '' city_name, '' image_name, '' tutor_code FROM city UNION SELECT institute_name AS keyword, 'Institute' AS col, '' city_name, '' image_name, '' tutor_code FROM institutes AS i LEFT JOIN address a ON i.address_id = a.address_id LEFT JOIN city c2 ON a.city_id = c2.city_id LEFT JOIN institute_images ii ON i.institute_id = ii.institute_id AND ii.image_type = 'profile' ) s WHERE keyword LIKE '%$queryString%' LIMIT 10 this query work about 50% but cant get to work in 100%. Problem is it is not display city name and profile image under institutes category, cant display district name and category names under cities and subjects fields. And also I would like to this kind of query is ineffective and does it waste time and resources? Thank you.
  20. Hey all I'm trying to do is echo out the column names from my sql table rather than the content. Everyone online seems to recommend mysql_fetch_field() but phps documentation say it's becoming deprecated now ? And other examples use quite a bit of code. Is there no simple way of doing this?
  21. MYSQL version: 5.5.27-cll - MySQL Community Server (GPL) Hey I have a simple articles and comments table. I want to display the articles along with their comments. I want to union with one select with the comments and another select without the comments on the same table. I have article number 1 with 1 comment and article number 2 with no comments and article number 3 with 2. I use the following query to get the results. SELECT articles.id AS article_id, comments.id AS comment_id, comment FROM articles LEFT JOIN comments ON comments.aid = articles.id UNION ALL SELECT articles.id AS article_id, NULL, NULL FROM articles GROUP BY article_id ORDER BY article_id DESC The result I get which is correct: article_id | comment_id | comment 3 | 3 | good 3 | 2 | very good 3 | NULL | NULL 2 | NULL | NULL 2 | NULL | NULL 1 | NULL | NULL 1 | 1 | | bad one Now if I want to count the comments also I add COUNT to the query and it becomes: SELECT articles.id AS article_id, comments.id AS comment_id, comment , COUNT(DISTINCT comments.id) AS count_comments FROM articles LEFT JOIN comments ON comments.aid = articles.id UNION ALL SELECT articles.id AS article_id, NULL, NULL , NULL FROM articles GROUP BY article_id ORDER BY article_id DESC Now the results change after adding the the count column and not all rows are outputted: article_id | comment_id | comment 3 | NULL | NULL 2 | NULL | NULL 1 | NULL | NULL 1 | 1 | bad one Now the comments aren't displayed except the comment of article 1, ID(2) should be displayed twice for the 2 select commands, and ID(3) should be displayed 3 times (1 for the second select command and 2 for the first select commands as there are 2 comments) I don't know why adding count leads to ths. Thanks
  22. Hey I have a simple articles table with IDs. I want to get the highest and lowest ids from the latest 10 results. For example, if there are 11 ids, the result should be 2 and 11 and if there are 4 ids, should be 4 and 1 and so on. I use PHP PDO statements. $aid = $DBH->prepare("SELECT id FROM articles ORDER BY id DESC LIMIT 10"); $aid->execute(); $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST); $lowest_article_id = $row[0]; $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_FIRST); $highest_article_id = $row[0]; The above will always return 11 if there are 11 records and 10 if there are 10 records. So, the query ignores the PDO::FETCH_ORI_LAST and PDO::FETCH_ORI_FIRST part of the query. Thanks
  23. hi all, im making a CMS and the website uses a php page main to display pages stored in the sql database! now my problem is that everything works fine but wen i call a page from de db with html and php code it will not display the php code, i work whit str_replace to replace %php_open% to <? and %php_close% to ?> because sql don't saves <?php i write the pages as %php_open% tot save in cms. this works fine and when i want to open the page all htm is visable but the php is notm when i open the source in the browser i see the php nice and well as it should be. what am i doing wrong to show the php ?? SOURCE CODE: </td> <td width="98%"> <br /> < b>Notice</b>: Undefined index: admin in <b>/home/vhosts/pc-hulp-online.nl/subdomains/test/httpdocs/main.php</b> on line <b>16</b><br /> <? function getIp() { if( isset( $_SERVER[ "HTTP_X_FORWARDED_FOR" ] ) ) { $return = $_SERVER[ "HTTP_X_FORWARDED_FOR" ]; } else if( isset( $_SERVER[ "HTTP_CLIENT_IP" ] ) ) { $return = $_SERVER[ "HTTP_CLIENT_IP" ]; } else { $return = $_SERVER[ "REMOTE_ADDR" ]; } return $return; } ?> <H1>Uw external IP:</H1><P>Deze is door uw provider aan u toegewezen, en kan nodig zijn bij speciaal ontwikkelde software zoals bijv. een chat server of andere servers zoals een FTP of HTTP server, of bij Remote desktop services </P><P>Uw huidige external IP Adres is: <? getIp(); ?></P><P><FONT color=#ff0000>Copyright by pc-hulp-online.nl ©®20013 </P></FONT> </td> <td width="1%"> </td> this ia exactly what it should be after getting it out off the db and replaced the %php_open% strings why don't display the IP??? can someone help me?? thnx jamie kock (holland)
  24. Hi all, The SQL I'm looking at calculates the the average age within the select and this works fine: (AVG((TO_DAYS(NOW())-TO_DAYS(pl.dob)))/365.242199) AS averageage The problem is when I want to count the ages that are within age groups. The example below should give the total number of ages within the 18-24 group. I can't get this to work, it displays zeros when the select statement is executed, there aren't any sql errors. SUM((case when (TO_DAYS(NOW())-TO_DAYS(pl.dob)/365.242199) BETWEEN 18 AND 24 then 1 else 0 end)) AS '18-24', Can anyone suggest a solution to this? CaptainChainsaw
  25. To start off with I am really sorry if the title doesn't necessarily describe what I am trying to accomplish here and really didn't know how to title it. What I have is a form that user inputs information and then when they hit submit it takes them to the page with the code to insert the information into the sql server database. This page also, will send an email out with the some of the information based on one of the fields the user input information into. When the information is inserted into the database, I have a column named RefNumber, that the column settings for it are as follows: [RefNumber] [int] IDENTITY(1,1) NOT NULL I want to have this RefNumber displayed out to the user as well as have it in the emails that are sent out as a confirmation for that the email is associated with. Here is my code that I currently and if any let me know if I need to explain anything more clearly: <?php session_start(); if (!(isset($_SESSION['forteid']) && $_SESSION['forteid'] != '')) { header ("Location: login.php"); exit(); } ?> <?php error_reporting(E_ALL ^ E_NOTICE); include 'includes/db_connect.php'; $CallDispo = $_POST['disposition']; $NewNotes = str_replace("'", "", "$_POST[notes]"); if (trim($CallDispo) == "Revision" or trim($CallDispo) == "Revision/Save" or trim($CallDispo) == "Revision/Collection" or trim($CallDispo) == "Reinstatement") { $to = "[email protected]"; $subject = "Revision - $_POST[appnumber]"; $message = "<Table border='1px' cellpadding='5' style='border:1px solid black; text-align: Left;'> <TR style='border:1px solid black; text-align: Left;'><td>ForteID:</td><td>$_POST[ForteID]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>AppNumber:</td><td>$_POST[appnumber]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Date:</td><td>$_POST[date]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Contract Number:</td><td>$_POST[Con_Number]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Finance Number:</td><td>$_POST[Finance_Num]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Phone Number:</td><td>$_POST[Phone_Num]</td></tr> <TR style='border:0px solid black; text-align: Left;'><td></td><td></td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Discount Amount:</td><td>$_POST[Disc_Amount]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>New Total Cost:</td><td>$_POST[Total_Cost]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Total Monthly Payments:</td><td>$_POST[Total_MP]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>New MP Amount:</td><td>$_POST[New_MP_Amt]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>New DP Amount:</td><td>$_POST[New_DP_Amt]</td></tr> <TR style='border:0 solid black; text-align: Left;'><td></td><td></td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Notes:</td><td>$NewNotes</td></tr>"; } elseif (trim($CallDispo) == "Escalation"){ $to = "[email protected]"; $subject = "Escalation - $_POST[appnumber]"; $message = "<Table border='1px' cellpadding='5' style='border:1px solid black; text-align: Left;'> <TR style='border:1px solid black; text-align: Left;'><td>ForteID:</td><td>$_POST[ForteID]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>AppNumber:</td><td>$_POST[appnumber]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Date:</td><td>$_POST[date]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Contract Number:</td><td>$_POST[Con_Number]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Finance Number:</td><td>$_POST[Finance_Num]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Phone Number:</td><td>$_POST[Phone_Num]</td></tr> <TR style='border:0px solid black; text-align: Left;'><td></td><td></td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Notes:</td><td>$NewNotes</td></tr>"; } elseif (trim($CallDispo) == "Email Contract"){ $to = "[email protected]"; $subject = "Email Contract - $_POST[appnumber]"; $message = "<Table border='1px' cellpadding='5' style='border:1px solid black; text-align: Left;'> <TR style='border:1px solid black; text-align: Left;'><td>ForteID:</td><td>$_POST[ForteID]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>AppNumber:</td><td>$_POST[appnumber]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Date:</td><td>$_POST[date]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Contract Number:</td><td>$_POST[Con_Number]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Phone Number:</td><td>$_POST[Phone_Num]</td></tr> <TR style='border:0px solid black; text-align: Left;'><td></td><td></td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Notes:</td><td>$NewNotes</td></tr>"; } elseif (trim($CallDispo) == "Send Contract/Invoice"){ $to = "[email protected]"; $subject = "Send Contract/Invoice - $_POST[appnumber]"; $message = "<Table border='1px' cellpadding='5' style='border:1px solid black; text-align: Left;'> <TR style='border:1px solid black; text-align: Left;'><td>ForteID:</td><td>$_POST[ForteID]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>AppNumber:</td><td>$_POST[appnumber]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Date:</td><td>$_POST[date]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Contract Number:</td><td>$_POST[Con_Number]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Finance Number:</td><td>$_POST[Finance_Num]</td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Phone Number:</td><td>$_POST[Phone_Num]</td></tr> <TR style='border:0px solid black; text-align: Left;'><td></td><td></td></tr> <TR style='border:1px solid black; text-align: Left;'><td>Notes:</td><td>$NewNotes</td></tr>"; } $headers = 'From: [email protected]' . "\r\n" . 'Reply-To: [email protected]' . "\r\n" . 'MIME-Version: 1.0' . "\r\n" . 'Content-type: text/html; charset=iso-8859-1' . "\r\n" . 'X-Mailer: PHP/' . phpversion(); //Insert to form data into database $tsql = "INSERT INTO logs(ForteID, disposition, appnumber, Finance_Num, num_payments, ach_cc, date, notes, Phone_Num, Cancel_Disposition, Con_Number, Post_Date, Total_Cost, Total_MP, New_MP_Amt, New_DP_Amt, Disc_Amount, Callback) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; $parameters = array( $_POST[ForteID], $_POST[disposition], $_POST[appnumber], $_POST[Finance_Num], $_POST[num_payments], $_POST[ach_cc], $_POST[date], $NewNotes, $_POST[Phone_Num], $_POST[Cancel_Disposition], $_POST[Con_Number], $_POST[Post_Date], $_POST[Total_Cost], $_POST[Total_MP], $_POST[New_MP_Amt], $_POST[New_DP_Amt], $_POST[Disc_Amount], $_POST[Callback]); $stmt = sqlsrv_query($connection, $tsql, $parameters); // Send Email if(mail($to, $subject, $message, $headers)) echo "Email sent!<br>"; else echo "No email to be sent!<br>"; /* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $connection); header( "refresh:2;url=index.php" ); echo 'Your entry has been saved!'; exit(); ?> Thanks in advance!
×
×
  • Create New...

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.