Jump to content

Search the Community

Showing results for tags 'mysql'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • 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 (Dreamweaver, Zend, 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


Last Updated

  • Start


Filter by number of...


  • Start





Website URL








Donation Link

Found 328 results

  1. wrote a stored procedure this morning and i don’t know how to get the values out of it through a class function in php or phpmyadmin. here is what i wrote : public function totalProcedures($friend_name,$session_id) { /* *query to fetch stored procedure */ try { //executing the stored procedure $sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)"; $stmt_sp= $this->_db->prepare($sql_sp); $stmt_sp->bindValue(":friend",$friend_name); $stmt_sp->bindValue(":session",$session_id); $stmt_sp->execute(); $rows=$stmt_sp->fetch(PDO::FETCH_ASSOC); $stmt_sp->closeCursor(); // closing the stored procedure //trying to get values from OUT parameters. $stmt_sp_2=$this->_db->prepare("select @updates,@group_posts"); $stmt_sp_2->execute(); return $stmt_sp_2->fetch(PDO::FETCH_ASSOC); } catch (PDOException $ei) { echo $ei->getMessage(); } } can someone helpme how to get results. here is the storedprocedure: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555)) BEGIN select * FROM updates where author in (friend,session_id) order by time desc limit 5; select * FROM group_posts where author_gp in (friend,session_id) order by pdate desc limit 5; END$$ DELIMITER ; i get the result in php myadmin as follows: how do i do this inside a php class function. CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts);
  2. So I have a database that is structured like this: https://imgur.com/a/DdyTqiE Sample data: https://imgur.com/a/kYwmuO1 For each appointment, a student can have multiple categories, such as 'Academic Probation, Re-Admit' etc... I would like to loop through this table, and get a count of how many were 'is_no_show' and 'is_cancelled' per (unique) category with respect to 'scheduled_student_services.' For example, in the sample data, we see that the first appointment has 4 categories (Academic Probation, Entering Cohort Year 20051, First Generation, and Re-Admit'). Now one of these categories - Academic Probation matches up with what they were scheduled for - '1st Term Probation Advising'. And they cancelled this appointment, so we want the count of cancellations under Academic Probation to go up by 1. How would I approach this? I know I probably need to do two loops but I'm not sure the PHP syntax for this. Any suggestions or tips would be helpful. Thank you for your time!
  3. Hi, I have created a quiz and the questions get read from a MySql database table. I am now wanting to stop it repeating questions, when I first started this I thougt it would be easy just create a column called Duplicate and everytime a question got pulled I would mark it in the databse. So it would only read questions that had a "0" in the DuplicateCol and after reading the question it would put a "1" in place of the "0" That would work, the problem being multiple people are using it so if a 1 is there they wont all get asked the question. Anyway that idea is a fail now, which is a shame because everything else worked. Im hoping someone can show me another way on stopping duplicates. when I created the quiz I created a registration, and login page which have there own table I then created another table for the questions with multiple columns ie " id, Question, Answer1, Answer2, Answer3, CorrectAnswer, DuplicateCol " I had a number on my form which is a random number that lets say is "6" will then pull the question from column id 6 Query = "select Question,Answer1,Answer2,Answer3,CorrectAnswer,DuplicateCol from Questions where DuplicateCol= '0' AND id='" + RandomN.Text + "'" Anyway im for the time being lost until someone can give me ideas please, The registration, login table looks like this id, Serial, Email, UserName, Location, UserScore, Activated, I was thinking of adding q1, q2, q3, q4, q5, etc etc and then somehow if a question got pulled in my questions table with id "6" then put a "1" in the column but I think it might be to hard to try and cross reference 2 tables. Any Ideas ?
  4. Hi, I have a MySql query which currently looks like this Query = "select Question,Answer1,Answer2,Answer3,CorrectAnswer,id,Duplicate from Questions where id='" + RandomN.Text + "'" As you can see I have a table called 'Questions' which has several columns, Im using VB.NET I also have a textBox on a windows form called 'RandomN' What this does is it takes whatever number is in my textBox called RandomN.Text and looks for that number in the id column and returns all data on that row. What I am trying to acheive now is this, I have a column called Duplicate it will either contain the word 'True' or 'False' I would like it to only return data from the given number in the textBox if the Duplicate column in that row contains the word 'False' If someone can shed some light I would be greatful. Thanks
  5. HI, Why can't I import a table into my existing DB using the command below: mysql -u root -p mydatabase < file.sql; This has always worked but now I get the following error. mysql version is Thanks !
  6. Hello Everyone - I am playing around with some MYSQL and PHP project I have. I ran into a complex problem getting a PHP table filled with data from MYSQL. I will try to explain what I am trying to do: I am trying to do something like this but in PHP. This is my data from MYSQL database. The Table is called Children This is a quick explanation of how each column on the first screenshot should be filled from the database. This code is what I have so far... to be honest i am not sure how to get the totals of rest of the columns. Maybe use I can use subqueries or if statements... not sure! Can you please help me out? $r = mysqli_query($dbc,"SELECT Classrooms.ClassroomName, COUNT(*) AS TotalChildren FROM Children JOIN Classrooms ON Children.classroomID = Classrooms.classroomID GROUP BY Classrooms.ClassroomName");
  7. I'm a newbie, can you please help? I'm getting a mysql error "Unknown column 'E0000001' in 'where clause'" The id is in the URL: ...user-profile.php?id=E0000001 My Query $query = mysqli_query($con, "SELECT * FROM UserList WHERE UserID=".$id) or die (mysqli_error($con)); Thank you
  8. I am trying to centralize my database communicatin into a class (im getting tired of typing and retyping the code to send queries) Its not done but I cant get INSERT to work. Heres my Class: <?php ###################################################### # Class Name: DBConnect # Description: Base Class to handle DB inquiries # Created: 06/27/19 # Updated: 06/27/19 # Author: James 'Karæthon' Cantando # Contact: TheKaraethon@gmail.com ###################################################### class DBConnect{ ###################################################### # PROPERTIES ###################################################### # PRIVATE private const HOST = 'localhost'; private const USER = 'root'; private const PASS = ''; private const DB = 'crackthecode'; private $link; private $table; # PUBLIC ###################################################### # METHODS ###################################################### # CONSTRUCTOR function __construct($target){ $this -> table = $target; $this -> link = mysqli_connect(self::HOST,self::USER,self::PASS,self::DB); if(!$this->link){ die("An error occured while attempting to connect to the table \"{$this -> table}\" on the ".self::DB." database.<br />Error#: ".mysqli_connect_errno()."<br />Description: ".mysqli_connect_error()); } } # PRIVATE private function checkValue($val){ switch(strtoupper($val)){ case 'NULL': return 'NULL, '; break; case 'CURRENT_TIMESTAMP': return 'CURRENT_TIMESTAMP, '; break; default: return "`".$val."`, "; } } # PUBLIC public function getData($criteria){ $query = "SELECT * FROM {$this->table} WHERE "; $crit = ""; foreach($criteria as $column => $value){ if(($column === 'bind')){ $crit .= "{$value} "; }else{ $crit .= "{$column} = {$value} "; } } $result = mysqli_query($this->link, $query.$crit); if(mysqli_num_rows($result) !== 0){ return $result; }else{ return false; } } public function newData($data){ $query = "INSERT INTO `{$this->table}` "; $cols = "("; $vals = "("; foreach($data as $col => $val){ $cols .= "`".$col."`, "; $vals .= $this->checkValue($val); } $query = $query.substr($cols,0,-2).") VALUES ".substr($vals,0,-2).")"; echo $query; echo "<br />INSERT INTO `players` (`PlayerID`, `CreatedDate`, `Username`, `Passcode`, `Email`, `FName`, `Lname`, `Addr1`, `Addr2`, `City`, `State`, `Country`, `PostalCode`, `Phone`, `PhoneType`, `TaxpayerID`, `DOB`, `TokenBalance`) VALUES (NULL, CURRENT_TIMESTAMP, 'test', 'ggb', 'hyh', 'yjj', 'hjj', 'ghu', 'ghj', 'tuo', 'dgi', 'fgu', 'iyh', 'ghk', 'Other', 'tujk', '2019-6-17', '0')"; $insert = mysqli_query($this->link, $query); //$insert = mysqli_query($this->link, "INSERT INTO `players` (`PlayerID`, `CreatedDate`, `Username`, `Passcode`, `Email`, `FName`, `Lname`, `Addr1`, `Addr2`, `City`, `State`, `Country`, `PostalCode`, `Phone`, `PhoneType`, `TaxpayerID`, `DOB`, `TokenBalance`) VALUES (NULL, CURRENT_TIMESTAMP, 'test', 'ggb', 'hyh', 'yjj', 'hjj', 'ghu', 'ghj', 'tuo', 'dgi', 'fgu', 'iyh', 'ghk', 'Other', 'tujk', '2019-6-17', '0')"); if(!$insert){ echo "An error occured while attempting to INSERT into the table \"{$this -> table}\" on the ".self::DB." database.<br />Error#: ".mysqli_connect_errno()."<br />Description: ".mysqli_connect_error(); } return $insert; } ###################################################### # End Class ###################################################### } ?> The testing page has this: <?php include '../includes/dbconn.class'; $test = new DBConnect("players"); $inserted = $test->newData([ 'playerID'=> 'NULL', 'CreatedDate'=> 'CURRENT_TIMESTAMP', 'Username'=>'test', 'Passcode'=>'123456789', 'Email'=>'test@test.test', 'FName'=>'test', 'Lname'=>'tested', 'Addr1'=>'1234 msin st', 'Addr2'=>'NULL', 'City'=>'Anytown', 'State'=>'Denial', 'Country'=>'United States', 'PostalCode'=>'12345-6789', 'Phone'=>'19995551212', 'PhoneType'=>'Other', 'TaxpayerID'=>'123-45-6789', 'DOB'=>'2019-6-17', 'TokenBalance'=>'1000000000' ]); if($inserted){echo 'Insert succeeded.';}else{echo 'Insert failed.';} ?> In the class file you will see two queries, one code generated the other from a successful phpmyadmin query. The phpmyadmin one works everytime, but the generated doent. and I cant see any difference between them when echoed except the values being inserted. please what am I doing wrong? Output from echoed queries: Generated Query: INSERT INTO `players` (`playerID`, `CreatedDate`, `Username`, `Passcode`, `Email`, `FName`, `Lname`, `Addr1`, `Addr2`, `City`, `State`, `Country`, `PostalCode`, `Phone`, `PhoneType`, `TaxpayerID`, `DOB`, `TokenBalance`) VALUES (NULL, CURRENT_TIMESTAMP, `test`, `123456789`, `test@test.test`, `test`, `tested`, `1234 msin st`, NULL, `Anytown`, `Denial`, `United States`, `12345-6789`, `19995551212`, `Other`, `123-45-6789`, `2019-6-17`, `1000000000`) PHPMyAdmin Query: INSERT INTO `players` (`PlayerID`, `CreatedDate`, `Username`, `Passcode`, `Email`, `FName`, `Lname`, `Addr1`, `Addr2`, `City`, `State`, `Country`, `PostalCode`, `Phone`, `PhoneType`, `TaxpayerID`, `DOB`, `TokenBalance`) VALUES (NULL, CURRENT_TIMESTAMP, 'test', 'ggb', 'hyh', 'yjj', 'hjj', 'ghu', 'ghj', 'tuo', 'dgi', 'fgu', 'iyh', 'ghk', 'Other', 'tujk', '2019-6-17', '0') notes mysql version: 10.0.33-MariaDB (? is that correct?)
  9. I have the following function: public function getData($criteria){ $query = "SELECT * FROM {$this->table} WHERE "; $crit = ""; foreach($criteria as $column => $value){ if($column = 'bind'){ $crit .= "{$value} "; }else{ $crit .= "{$column} = {$value} "; } } echo $crit; } I'm testing it with getData(['test1'=>'test2', 'bind'=>'AND', 'test3'=>'test4']); But instead of getting "test1 = test2 AND test3 = test4" the echo output is "test2 AND test4". I'm probably too close to see whats wrong, what do you see?
  10. I'm on a new path here and would appreciate any ideas you pros might have. I think this involves triggers and another table but am not sure. I have a project record that has fields that contain information. It's important to know when some fields change. For example, I have a field call DRAWING which contains a link to a project print. Values in this link change whenever the print is modified. It's important to know the date of the last change. When the project page is opened for review, I want to show, to the right of this field (and a few others), the date/time is was last changed. I want the time information field based, not record based. My initial idea is to trigger after a record update and compare an 'identical' table against the project table. After checking the fields of interest for change I would write the project table associated lastupdate fields with a date and then overwrite this 'identical' table with the new state of things. This all could be done with code but it gets a bit onerous. I was hoping to do it once in a stored procedure. Comments? Thank you. 10.1.38-MariaDB
  11. When I run this I am getting no errors and no data back please can someone tell me what i am doing wrong <?php include 'conn.php'; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); //Query $sql = $conn->prepare("SELECT hotelid, hotelname FROM Hotels WHERE hotelid = '1'"); $sql->execute() ; ?> <div id="container"> <header> <div id="header"> <div class="h1"> <?php while( $row = $sql->fetch()) : ?> <h1><span><?php echo $row['hotelname']; ?></span> <?php endwhile ?>
  12. I'm currently making use of arrays to modify my font colors in my chat script for example $admin = array('m70141099002' , 'Debater', '27765238453', ''); $moderator = array('m52626156002', 'm57010835002' , 'm50079252002', '27723289551', 'm38565659002'); $testip = $_SERVER["HTTP_X_MXIT_USERID_R"]; if(!isset($name)) { $name = "Debater"; } if(!isset($testip)) { $testip = "Debater"; } ////////////////This is for Admin Permission Users////////////// if (in_array($testip, $admin)) { if (in_array($list['StringyChat_ip'], $admin)) { print '<span style="color:#828282">' . '(' . date( 'D H:i:s', $list['StringyChat_time'] ) . ') ' . '</span>' . $form . ' ' . '</span>'. '<span style="color:red">' . '<b>' . $list['StringyChat_name'] . '</b>' . ' : ' . filterBadWords(wordwrap($list['StringyChat_message'], $line_length,"<br>\n")) . '</span>' . '<span style="color:#d8d8d8">' . " - " . $ipi . '</span>' . '<br />'; } elseif (in_array($list['StringyChat_ip'], $moderator)) { print '<span style="color:#828282">' . '(' . date( 'D H:i:s', $list['StringyChat_time'] ) . ') ' . '</span>' . $form . ' ' . '<span style="color:green">' . '<b>' . $list['StringyChat_name'] . '</b>' . ' : ' . filterBadWords(wordwrap($list['StringyChat_message'], $line_length,"<br>\n")) . '</span>' . '<span style="color:#d8d8d8">' . " - " . $ipi . '</span>' . '<br />'; } elseif (in_array($list['StringyChat_ip'], $pc)) { print '<span style="color:gold">' . '(' . date( 'D H:i:s', $list['StringyChat_time'] ) . ') ' . '</span>' . $form . ' ' . '<span style="color:purple">' . '<b>' . $list['StringyChat_name'] . '</b>' . '</span>' . ' : ' . '<span style="color:blue">' . filterBadWords(wordwrap($list['StringyChat_message'], $line_length,"<br>\n")) . '</span>' . '<span style="color:#d8d8d8">' . " - " . $ipi . '</span>' . '<br />'; } elseif (in_array($list['StringyChat_ip'], $helper)) { print '<span style="color:#828282">' . '(' . date( 'D H:i:s', $list['StringyChat_time'] ) . ') ' . '</span>' . $form . ' ' . '<span style="color:blue">' . '<b>' . $list['StringyChat_name'] . '</span>' . '<span style="color:green">' . ' ' . '[H]' . '</span>' . '</b>' . ' : ' . '<span style="color:blue">' . filterBadWords(wordwrap($list['StringyChat_message'], $line_length,"<br>\n")) . '</span>' . '<span style="color:#d8d8d8">' . " - " . $ipi . '</span>' . '<br />'; } elseif (in_array($list['StringyChat_ip'], $globalhelper)) { print '<span style="color:#828282">' . '(' . date( 'D H:i:s', $list['StringyChat_time'] ) . ') ' . '</span>' . $form . ' ' . '<span style="color:blue">' . '<b>' . $list['StringyChat_name'] . '</span>' . '<span style="color:green">' . ' ' . '[G.H]' . '</span>' . '</b>' . ' : ' . '<span style="color:blue">' . filterBadWords(wordwrap($list['StringyChat_message'], $line_length,"<br>\n")) . '</span>' . '<span style="color:#d8d8d8">' . " - " . $ipi . '</span>' . '<br />'; } else { print '<span style="color:#828282">' . '(' . date( 'D H:i:s', $list['StringyChat_time'] ) . ') ' . '</span>' . $form . ' ' . '<span style="color:#0365B8">' . '<b>' . $list['StringyChat_name'] . '</b>' . '</span>' . ' : ' . filterBadWords(wordwrap($list['StringyChat_message'], $line_length,"<br>\n")) . '<span style="color:#d8d8d8">' . " - " . $ipi . '</span>' . '<br />'; } } This printout will show all the users matching the testip within the admin array, I got the same printout for moderators as well. Well the real problem I'm experiencing right now is to use my database to calculate the color it should display. I got 3 tables at this stage Table 1: Users2 Layout ID, Username, mxitid, nick, phone and rank This table is where I store the usernames of people if they register and give them the rank value of 6 Table 2: ranks_colors Layout Type, rank, color1, color2, color3, color4 Admin, 1, red, blue, orange, purple Mod, 2, green, pink, yellow, black etc... upto 6 wich is Normal This table is where I specify Type like Administrator rank 1 and 4 colors he can use within hes text printout Table 3: StringyChat Layout id, ip, name, message, time, device, rank This is basically where the messages that is being send will be stored each in its own row with the specified rank number. The problem I'm currently struggling with is the ranks I want each rank type font to be different for example in the above Table 2. If admin all the messages send by admin must be red. All the mod messages must be green etc. How can I change from the array way to mysql by using the ranks system?
  13. I have this table: CREATE TABLE _HRatortbl ( Hor_id INT unsigned NOT NULL auto_increment, Hicator_title longtext NOT NULL, Primary KEY(HRindicator_id), UNIQUE KEY ix_length_HRindicator_title (HRindicator_title(255)) )ENGINE=InnoDB DEFAULT CHARSET=utf8 why is it that it cannot differentiate between: Legally stipulated Age for marriage and legally stipulated age for mariage I found them both iniside the table .Is there another way to do this on text fields
  14. So, I currently have a script (in beta mode, only works when the sun shines), that can upload an image and store directly in a mysql database (using the BLOB data type); the script is also able to retrieve the BLOB data and display them vertically on the page; I actually need to dsiplay them horizontally across the page instead. So currently the retrieved images are printed in this format: 1 2 3 4 5 6 7 8 and I need it to print this way: 1 2 3 4 5 6 7 8 sounds like a for loop somewhere... any takers on how to implement the feature?
  15. I am getting Fatal error: Uncaught Error: Call to a member function real_query() on null with this code: public final function Retrieve($TABLE, $CRIT){ $_query = "SELECT * FROM `{$TABLE}` WHERE "; foreach($CRIT as $_field => $info){ $_query .= " `{$_field}` = `{$info}` &&"; } if($this->LINK->real_query(rtrim($_query, ' &'))){ return $this->LINK->store_result(); } else{ return json_encode(array("Error"=>$this->LINK->errno(), "Description"=>$this->LINK->error())); } } (LINK is my mysql_connect() result.) I have tried everything i can think of, ->query, going to mysqli_query, breaking it sown and using a $result variable, but nothing seems to work...
  16. <?php $database = array(); $database['host'] = "localhost"; $database['port'] = '3306'; $database['name'] = "forumtest"; $database['username'] = "root"; $database['password' = "Password"; $link = mysql_connect($database['host], $database['username'], $database['password']); if ($link) { echo "Connected to a database".$database['name']; }else{ echo connect to a database"$database['name'] . "failed<br/>"; echo "Error: ".mysql_error(); } ?> This is my code for a test to connect to a database however, there seems to be a problem with the code as when I try to connect it just gives me a error 500. I would appreciate any help.
  17. hello dear php-experts, i am currently workin on a litte contact manager. and i want to store the data in a db. Note: i work with Python at the moment. So this is related to Python since i want to dive into Peewee. What is aimed: i am musing on how to insert a list of tuples into the db using Peewee (/note the pyton object-layer-model). My db is setup as follows: class Stats(Model): name = TextField(index=True) gender = TextField() age = TextField() city = TextField() state = TextField() class Meta: database = db My list of tuples looks like this: records = [("Joe Smoe", "Male", 34, "Joe Fracer", " Staten Island")], [("Jane Doe", "Female", 21, "Jane bell", "Capetown")] Well - i wonder how to work out the concrete db-inserts. Should I iterate over the list inserting one row at a time? Can this be bulk inserted or does it need to be made into a dictionary to achieve that solution? i have rows like so (see below). I can do this manually. Of course: rows = [ {"name": "Joe Jackson", "gender": "Male", "age": 44, "city": "Billi Kid ", "state": "Mosqow"}, {"name": "Howard Foster", "gender": "Female", "age": 22, "city": "Jane Austen", "state" :"Capetown"}, ... ] Well - one can do this manually. But honestly: isnt it much better and a lot more efficient to use peewee's Model.insert_many function to do a bulk insert of the data into the database - well with a single SQL INSERT statement? Love to hear from you experts, your dilbert!
  18. I have a Mysql table where I store prices of a product. I have the values in "varchar" format because I needed to include "decimals" in the prices(for eg. 15.30) and "int" doesn't allow that. Having done that, when ever I filter the products by prices in Ascending or Descending order, they don't show up in proper order. It seems like the decimal in the prices is messing up the order. Is there a way to fix this so that the php query can filter the prices in proper order despite the decimals? Here's the eg of the query. $get_records = $db->prepare("SELECT * FROM records ORDER BY records.price DESC");
  19. I'm trying to convert these 2 existing select queries: //existing records that are expired $expiredCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.build = :build AND s.cov = :cov AND s.col = :col AND p.expire_date <= date_add(convert(:ship,date), interval 7 day) "; //existing records that are not expired $validCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.build = :build AND s.cov = :cov AND s.col = :col AND p.expire_date > date_add(convert(:ship,date), interval 7 day) "; $checkExisting = $MysqlConn->prepare($expiredCheck); $checkExistingValid = $MysqlConn->prepare($validCheck); $existingRslt = $checkExisting->execute($values2); $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC); $existingVldRslt = $checkExistingValid->execute($values2); $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC); Into one general select like so: //select records alltogether, check for expiration later in loop $expiredCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.build = :build AND s.cov = :cov AND s.col = :col "; $checkExisting = $MysqlConn->prepare($expiredCheck); And then use my existing bound parameters ($values2) to formulate an if statement that will replace the previously existing expiration date check. Basically I've taken the 2 previous expiration date checks from the query and plugged them into the if statement but it fails the '7' in my day interval. I feel like there may be a different syntax standard to use here but I can't seem to find the proper solution. I'm trying to create a $count3 variable for the first check and a $count4 variable for the 2nd check. Here are params and if statement: $values2 = [ ":DEALER" => $row2["DEALER"], ":build" => $row2["build"], ":cov" => $row2["cov"], ":col" => $row2["col"], ":ship" => $row2["ship"], ]; $existingRslt = $checkExisting->execute($values2); while($existingRow = $checkExisting->fetch(PDO::FETCH_ASSOC)){ if($existingRow["expire_date"] <= date_add(convert(":ship",date), interval 7 day){ $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC); }elseif($existingRow["expire_date"] > date_add(convert(":ship",date), interval 7 day)){ $count4 = $checkExisting->fetch(PDO::FETCH_ASSOC); } } I'm just trying to consolidate SELECT queries if possible. What am I doing wrong?
  20. Hi all !! I have the following three data tables : mysql> select * from franch; +-----+ | fid | +-----+ | 3 | | 5 | | 7 | +-----+ 3 rows in set (0.00 sec) mysql> select * from master; +-----+-----+ | xid | mid | +-----+-----+ | 5 | 4 | | 7 | 6 | +-----+-----+ 2 rows in set (0.01 sec) mysql> select * from admin; +-----+-----+ | xid | aid | +-----+-----+ | 3 | 2 | | 4 | 2 | +-----+-----+ These are connected on common fields and I was trying to linearize the relationship between them using this query : mysql> SELECT vc.aid, vd.mid as mid, ve.fid as franch FROM franch as ve LEFT JOIN master as vd ON ve.fid = vd.xid LEFT JOIN admin as vc ON vd.mid = vc.xid; which gives the following , almost correct, output. +------------+------------+--------------+ | aid | mid | franch | +------------+------------+--------------+ | NULL | NULL | 3 | | 2 | 4 | 5 | | NULL | 6 | 7 | +------------+------------+--------------+ what I want to achieve as output is this ( difference HIGHLIGHTED in RED ): +---------+---------+---------+ | aid | mid | franch | +---------+---------+---------+ | 2 | NULL | 3 | | 2 | 4 | 5 | | NULL | 6 | 7 | +---------+---------+---------+ In the first since there is no corresponding vale for mid in the master table it produces the null value. Here there has to be a way that when such a null value is produced, the franch should check for a corresponding value in the admin table instead. Thanks all for any help on achieving this !!!
  21. I have a table full of mac addresses in this format 80828704B0EE I need a method to run a query that will output this format 80:82:87:04:B0:EE I either need to insert the result of the some query that is formatting the existing mac addresses to another field or use a query to format the mac addresses on the fly. I need to create a csv file from this query. I have all that code it is just making the query to format it the way I need it. Here is a query I found that does the trick except that I get two columns, the original and the formatted one. How can I remove the mac column that this outputs? SELECT mac, concat(left(mac,2), ":",substring(mac,3,2), ":" , substring(mac,5,2), ":",substring(mac,7,2), ":",substring(mac,9,2), ":", right(mac,2)) as mac_formatted FROM phones Thanks
  22. I have to say that this is not a coding question but is something i would like to hear your opinion: I am building a web service where the user registers and then can have access to this service. During his/her time using the service, the user collects some points according to his/her actions (similar to what Stackoverflow does). Question 1: Do we need to offer both DEACTIVATION and DELETE account? Question 2: In case of DEACTIVATION should i offer an option to re-activate their previous account [with all points gathered so far]? Meaning all their previous data are kept in the database, just change some flags? Is there a best practice for this? Question 3: In case of DELETE account, how do i proceed? Do i delete all database data regading that user? What if a user wants to delete his/her account because he/she gathered a lot of negative points and wants to re-register with the same email just with no negative points? Question 4: Do other services (like Facebook or Google+) delete any data from their databases even if the user wants to delete his/her account? Are there any legal issues? Thanks!
  23. I's trying to transform data from mysql into something like $books below but it doenst seem to be working $books = array( "phil" => array("my girl" => 2.5, "the god delusion" => 3.5, "tweak" => 3, "the shack" => 4, "the birds in my life" => 2.5, "new moon" => 3.5) ) this is how I tried doing: $sql = "SELECT * from rating where user_id=11 limit 5"; $db_result = mysql_db_query($dbname,$sql) or trigger_error(mysql_error()); $num_rows = mysql_num_rows($db_result) or trigger_error(mysql_error()); while ($row = mysql_fetch_array($db_result)) { $one = $row['bookId']; $two = $row['user_id']; $three = $row['rating']; $ArraY= array( $two => array($one=>$three) ); print_r($ArraY); } but this is what i get : Array ( [11] => Array ( [123715] => 5 ) ) Array ( [11] => Array ( [140329] => 5 ) ) Array ( [11] => Array ( [3083854] => 4 ) ) Array ( [11] => Array ( [871236761] => 1 ) ) Array ( [11] => Array ( [451179757] => 1 ) ) Array ( [11] => Array ( [451403886] => 3 ) ) Array ( [24] => Array ( [044661095X] => 4 ) ) Array ( [24] => Array ( [014010268X] => 1 ) ) Array ( [24] => Array ( [812576063] => 5 ) ) Array ( [24] => Array ( [038076654X] => 1 ) ) instead of something like: Array ( [phil] => Array ( [my girl] => 2.5 [the god delusion] => 3.5 [tweak] => 3 [the shack] => 4 [the birds in my life] => 2.5 [new moon] => 3.5 ) [sameer] => Array ( [the last lecture] => 2.5 [the god delusion] => 3.5 [the noble wilds] => 3 [the shack] => 3.5 [the birds in my life] => 2.5 [new moon] => 1 ) [john] => Array ( [a thousand splendid suns] => 5 [the secret] => 3.5 [tweak] => 1 ) [peter] => Array ( [chaos] => 5 => 3.5 ) [jill] => Array ( [the last lecture] => 1.5 [the secret] => 2.5 [the noble wilds] => 4 [the host: a novel] => 3.5 [the world without end] => 2.5 [new moon] => 3.5 ) [bruce] => Array ( [the last lecture] => 3 [the hollow] => 1.5 [the noble wilds] => 3 [the shack] => 3.5 [the appeal] => 2 [new moon] => 3 ) [tom] => Array ( [chaos] => 2.5 ) ) Any help with how i can transform that will be much appreciated THANKS
  24. Hi all, I am new to PHP and a bit slow in understanding ajax, javascript. I want to ask on how to display the table after I select the 3rd dropdown. I don't know how to start. //this is ajax-dd3.php file <!doctype html public "-//w3c//dtd html 3.2//en"> <html> <head> <title></title> <META NAME="DESCRIPTION" CONTENT=""> <META NAME="KEYWORDS" CONTENT=""> <script type="text/javascript"> function ajaxFunction(choice) { var httpxml; try { // Firefox, Opera 8.0+, Safari httpxml=new XMLHttpRequest(); } catch (e) { // Internet Explorer try { httpxml=new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) { try { httpxml=new ActiveXObject("Microsoft.XMLHTTP"); } catch (e) { alert("Your browser does not support AJAX!"); return false; } } } function stateChanged() { if(httpxml.readyState==4) { //alert(httpxml.responseText); var myObject = JSON.parse(httpxml.responseText); for(j=document.myForm.state.options.length-1;j>=0;j--) { document.myForm.state.remove(j); } var state1=myObject.value.state1; var optn = document.createElement("OPTION"); optn.text = 'Select State'; optn.value = ''; document.myForm.state.options.add(optn); for (i=0;i<myObject.state.length;i++) { var optn = document.createElement("OPTION"); optn.text = myObject.state[i]; optn.value = myObject.state[i]; document.myForm.state.options.add(optn); if(optn.value==state1){ var k= i+1; document.myForm.state.options[k].selected=true; } } ////////////////////////// for(j=document.myForm.city.options.length-1;j>=0;j--) { document.myForm.city.remove(j); } var city1=myObject.value.city1; //alert(city1); for (i=0;i<myObject.city.length;i++) { var optn = document.createElement("OPTION"); optn.text = myObject.city[i]; optn.value = myObject.city[i]; document.myForm.city.options.add(optn); if(optn.value==city1){ document.myForm.city.options[i].selected=true; } } /////////////////////////// document.getElementById("txtHint").style.background='#00f040'; document.getElementById("txtHint").innerHTML='done'; //setTimeout("document.getElementById('txtHint').style.display='none'",3000) } } var url="ajax-dd3ck.php"; var country=myForm.country.value; if(choice != 's1'){ var state=myForm.state.value; var city=myForm.city.value; }else{ var state=''; var city=''; } url=url+"?country="+country; url=url+"&state="+state; url=url+"&city="+city; url=url+"&id="+Math.random(); myForm.st.value=state; //alert(url); document.getElementById("txtHint2").innerHTML=url; httpxml.onreadystatechange=stateChanged; httpxml.open("GET",url,true); httpxml.send(null); document.getElementById("txtHint").innerHTML="Please Wait...."; document.getElementById("txtHint").style.background='#f1f1f1'; } </script> </head> <body > </head> <body> <div id="txtHint" style="width : 100px;background-color: #cccc33;">Message area</div> <br><br> <form name="myForm" action='ajax-dd3-details.php' method='post'"> <input type=hidden name=st value=0> <table width=500> <tr><td > Select Country<br><select name=country id='s1' onchange=ajaxFunction('s1');> <option value=''>Select One</option> <?Php //require "../include/z_db1.php"; require "config.php";// connection to database $sql="select distinct country from student5 "; foreach ($dbo->query($sql) as $row) { echo "<option value=$row[country]>$row[country]</option>"; } ?> </select> </td><td ><select name=state onchange=ajaxFunction('s2');> <option value=''>Select One</option></select></td> <td ><select name=city onchange=ajaxFunction('s3');> <option value=''>Select One</option></select></td> </tr></tr> <tr><td colspan=3><input type=submit value='Submit'></td></tr> </form> </table> <br><br> <div id="txtHint2"></div> </body> </html> //this is ajax-dd3ck.php file <?Php require "config.php"; // connection details error_reporting(0);// With this no error reporting will be there ////////// ///////////////////////////////////////////////////////////////////////////// $country=$_GET['country'];// //$country='IND'; // To check you can use this $state1=$_GET['state']; $city1=$_GET['city']; ///////////// Validate the inputs //////////// // Checking country variable /// if((strlen($country)) > 0 and (!ctype_alpha($country))){ echo "Data Error"; exit; } // Checking state variable (with space ) /// if ((strlen($state1)) > 0 and ctype_alpha(str_replace(' ', '', $state1)) === false) { echo "Data Error"; exit; } /////////// end of input validation ////// if(strlen($country) > 0){ $q_country="select distinct(state) from student5 where country = '$country'"; }else{ $q_country="select distinct(state) from student5"; } //echo $q_country; $sth = $dbo->prepare($q_country); $sth->execute(); $state = $sth->fetchAll(PDO::FETCH_COLUMN); $q_state="select distinct(city) from student5 where "; if(strlen($country) > 0){ $q_state= $q_state . " country = '$country' "; } if(strlen($state1) > 0){$q_state= $q_state . " and state='$state1'";} $sth = $dbo->prepare($q_state); $sth->execute(); $city = $sth->fetchAll(PDO::FETCH_COLUMN); $main = array('state'=>$state,'city'=>$city,'value'=>array("state1"=>"$state1","city1"=>"$city1")); echo json_encode($main); ////////////End of script ///////////////////////////////////////////////////////////////////////////////// ?> //this is ajax-dd3-details.php file <!doctype html public "-//w3c//dtd html 3.2//en"> <html> <head> <title></title> <META NAME="DESCRIPTION" CONTENT=""> <META NAME="KEYWORDS" CONTENT=""> </head> <body> <?Php echo "Country : $_POST[country]<br> State : $_POST[state]<br> City : $_POST[city]<br> <br><br><br> Return to <a href=ajax-dd3.php>Drop down list</a> "; ?> </body> </html>
  25. <!DOCTYPE html> <!-- To change this license header, choose License Headers in Project Properties. To change this template file, choose Tools | Templates and open the template in the editor. --> <html> <script src="https://code.jquery.com/jquery-1.10.2.js"></script> <div id="txtHint"></div> <script> function showUser(str) { // alert (str); if (str == "") { document.getElementById("txtHint").innerHTML = ""; return; } else { if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp = new XMLHttpRequest(); } else { // code for IE6, IE5 xmlhttp = new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange = function () { if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { document.getElementById("txtHint").innerHTML = xmlhttp.responseText; } } xmlhttp.open("GET", "new.php?q=" + str, true); xmlhttp.send(); } } </script> <body> <form> <select onchange="showUser(this.value)"> <option value="1" > 1 </option> <option value="3" > 3 </option> </select> <?php $con = mysqli_connect("localhost", "root", ""); mysqli_select_db($con, "crud_tutorial"); if (isset($_REQUEST['q'])) { $q = intval($_GET['q']); //echo "$q"; $sql = "SELECT * FROM customers WHERE id = '" . $q . "'"; $result = mysqli_query($con, $sql); } else { $sql = "SELECT * FROM customers "; $result = mysqli_query($con, $sql); } echo "<table> <tr> <th>id</th> <th>name</th> </tr>"; while ($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['name'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_close($con); ?> </form> </body> </html>
  • 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.