|
PHP - Mysql Export Fields Mapping
Similar TutorialsHello, How can I export mysql database (tables) with php from browser to backup.sql , something like in phpMyAdmin. And simple option with file save as??? Thanks Regards Hi, i have a table which shows some mysql data, every entry has a checkbox to select individual entries, now i want to be able to export those selected entries into a xml or txt file, i tried this: Code: [Select] <?php if($_POST['exporttxt']){ for($i=0;$i<count($_POST['checkbox']);$i++){ $export_id = $checkbox[$i]; $sql = "SELECT * FROM table WHERE id='$export_id'"; $result = mysql_query($sql);} $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n"; if($result->num_rows > 0) { while($myrow = $result->fetch_assoc()) { $output .= "\t<row>\n"; foreach($myrow as $_name => $_value) { $output .= "\t\t<$_name>$_value</$_name>\n"; } $output .= "\t</row>\n"; } } $output .= "</root>"; } header('content-type: text/xml'); header('content-disposition: attachment; filename=data_export.xml'); echo $output; exit; ?> but that doesn't seem to work. Any hints ? Goodevening.Thuis is the codes under these but i dont have any idea on how can i use it because i only use php and mysql as my database. i dont know how to use the xml as my database. can you help me? Thanks in advance
<?xml version="1.0" encoding="UTF-8" ?> <XMLDB PATH="plagiarism/programming/db" VERSION="20121207" COMMENT="XMLDB file for Moodle plagiarism/programming" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd" > <TABLES> <TABLE NAME="plagiarism_programming" COMMENT="This table saves settings for source code plagiarism detection in programming assignments" NEXT="plagiarism_programming_rpt"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="cmid"/> <FIELD NAME="cmid" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" COMMENT="The course module id" PREVIOUS="id" NEXT="scandate"/> <FIELD NAME="scandate" TYPE="int" LENGTH="10" NOTNULL="false" UNSIGNED="true" SEQUENCE="false" COMMENT="The date submissions are scanned" PREVIOUS="cmid" NEXT="jplag"/> <FIELD NAME="jplag" TYPE="int" LENGTH="1" NOTNULL="false" UNSIGNED="true" SEQUENCE="false" COMMENT="JPlag tool is used or not" PREVIOUS="scandate" NEXT="moss"/> <FIELD NAME="moss" TYPE="int" LENGTH="1" NOTNULL="false" UNSIGNED="true" SEQUENCE="false" COMMENT="MOSS tool is used or not" PREVIOUS="jplag" NEXT="language"/> <FIELD NAME="language" TYPE="char" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="Which language is used in the assignment" PREVIOUS="moss" NEXT="auto_publish"/> <FIELD NAME="auto_publish" TYPE="int" LENGTH="1" NOTNULL="false" UNSIGNED="true" SEQUENCE="false" PREVIOUS="language" NEXT="notification"/> <FIELD NAME="notification" TYPE="char" LENGTH="200" NOTNULL="false" SEQUENCE="false" COMMENT="Used to display notifications to students" PREVIOUS="auto_publish" NEXT="starttime"/> <FIELD NAME="starttime" TYPE="int" LENGTH="10" NOTNULL="false" UNSIGNED="false" SEQUENCE="false" COMMENT="The time when last plagiarism scanning occurred" PREVIOUS="notification" NEXT="latestscan"/> <FIELD NAME="latestscan" TYPE="int" LENGTH="10" NOTNULL="false" UNSIGNED="false" SEQUENCE="false" COMMENT="timestamp of the latest scan" PREVIOUS="starttime" NEXT="notification_text"/> <FIELD NAME="notification_text" TYPE="text" LENGTH="medium" NOTNULL="false" SEQUENCE="false" COMMENT="the customised notification text to appear on the student assignment page" PREVIOUS="latestscan"/> </FIELDS> <KEYS> <KEY NAME="primary" TYPE="primary" FIELDS="id"/> </KEYS> <INDEXES> <INDEX NAME="cmid_index" UNIQUE="false" FIELDS="cmid" COMMENT="Index for cmid for fast searching"/> </INDEXES> </TABLE> <TABLE NAME="plagiarism_programming_rpt" COMMENT="containing different report versions" PREVIOUS="plagiarism_programming" NEXT="plagiarism_programming_jplag"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="cmid"/> <FIELD NAME="cmid" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="id" NEXT="time_created"/> <FIELD NAME="time_created" TYPE="int" LENGTH="15" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" PREVIOUS="cmid" NEXT="version"/> <FIELD NAME="version" TYPE="int" LENGTH="11" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="time_created" NEXT="detector"/> <FIELD NAME="detector" TYPE="char" LENGTH="10" NOTNULL="false" SEQUENCE="false" PREVIOUS="version"/> </FIELDS> <KEYS> <KEY NAME="report_primary" TYPE="primary" FIELDS="id" COMMENT="Primary key of the table"/> </KEYS> <INDEXES> <INDEX NAME="cmiverdet_index" UNIQUE="false" FIELDS="cmid, version, detector" COMMENT="Index for cmid, version, detector for fast searching"/> </INDEXES> </TABLE> <TABLE NAME="plagiarism_programming_jplag" COMMENT="containing JPlag scanning status of the lastest report" PREVIOUS="plagiarism_programming_rpt" NEXT="plagiarism_programming_moss"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="submissionid"/> <FIELD NAME="submissionid" TYPE="char" LENGTH="20" NOTNULL="false" SEQUENCE="false" PREVIOUS="id" NEXT="status"/> <FIELD NAME="status" TYPE="char" LENGTH="20" NOTNULL="false" SEQUENCE="false" PREVIOUS="submissionid" NEXT="directory"/> <FIELD NAME="directory" TYPE="char" LENGTH="200" NOTNULL="false" SEQUENCE="false" PREVIOUS="status" NEXT="message"/> <FIELD NAME="message" TYPE="char" LENGTH="200" NOTNULL="false" SEQUENCE="false" PREVIOUS="directory" NEXT="settingid"/> <FIELD NAME="settingid" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" PREVIOUS="message" NEXT="progress"/> <FIELD NAME="progress" TYPE="int" LENGTH="3" NOTNULL="false" UNSIGNED="false" SEQUENCE="false" COMMENT="Show the progress (in percentage) of the current status" PREVIOUS="settingid" NEXT="token"/> <FIELD NAME="token" TYPE="char" LENGTH="32" NOTNULL="false" SEQUENCE="false" COMMENT="Contain a random token for security when a child process is initiated. This token is then compared with the token passed in to ensure it is a valid call" PREVIOUS="progress" NEXT="error_detail"/> <FIELD NAME="error_detail" TYPE="text" LENGTH="medium" NOTNULL="false" SEQUENCE="false" COMMENT="The detail of the error messages - for developer to see only" PREVIOUS="token"/> </FIELDS> <KEYS> <KEY NAME="primary" TYPE="primary" FIELDS="id"/> </KEYS> <INDEXES> <INDEX NAME="submissionid_index" UNIQUE="false" FIELDS="submissionid" COMMENT="Index for submissionid for fast searching"/> </INDEXES> </TABLE> <TABLE NAME="plagiarism_programming_moss" COMMENT="The table contains parameters and status for the last time the assignment is scanned with MOSS" PREVIOUS="plagiarism_programming_jplag" NEXT="plagiarism_programming_reslt"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="settingid"/> <FIELD NAME="settingid" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" PREVIOUS="id" NEXT="resultlink"/> <FIELD NAME="resultlink" TYPE="char" LENGTH="200" NOTNULL="false" SEQUENCE="false" PREVIOUS="settingid" NEXT="status"/> <FIELD NAME="status" TYPE="char" LENGTH="20" NOTNULL="false" SEQUENCE="false" PREVIOUS="resultlink" NEXT="message"/> <FIELD NAME="message" TYPE="text" LENGTH="small" NOTNULL="false" SEQUENCE="false" PREVIOUS="status" NEXT="progress"/> <FIELD NAME="progress" TYPE="int" LENGTH="3" NOTNULL="false" UNSIGNED="false" SEQUENCE="false" COMMENT="The progress (in percentage) of the lattest MOSS scanning" PREVIOUS="message" NEXT="token"/> <FIELD NAME="token" TYPE="char" LENGTH="32" NOTNULL="false" SEQUENCE="false" COMMENT="For verification of valid call when a forked process initiated by the main process. The token is first generated by the main process and stored in this field, and passed that token to the forked process. It is a valid call if the token passed is the same with the token stored" PREVIOUS="progress" NEXT="error_detail"/> <FIELD NAME="error_detail" TYPE="text" LENGTH="medium" NOTNULL="false" SEQUENCE="false" COMMENT="Detail of the encountered error. Valid only when status=error" PREVIOUS="token"/> </FIELDS> <KEYS> <KEY NAME="primary" TYPE="primary" FIELDS="id"/> </KEYS> <INDEXES> <INDEX NAME="settingid_index" UNIQUE="false" FIELDS="settingid" COMMENT="Index for settingid for fast searching"/> </INDEXES> </TABLE> <TABLE NAME="plagiarism_programming_reslt" COMMENT="store the result of the scanning, each record contains a pair" PREVIOUS="plagiarism_programming_moss" NEXT="plagiarism_programming_cours"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="student1_id"/> <FIELD NAME="student1_id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="id" NEXT="student2_id"/> <FIELD NAME="student2_id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="student1_id" NEXT="additional_codefile_name"/> <FIELD NAME="additional_codefile_name" TYPE="char" LENGTH="255" NOTNULL="false" SEQUENCE="false" COMMENT="not null when the pair match is a comparison between a student and an additional code file" PREVIOUS="student2_id" NEXT="similarity1"/> <FIELD NAME="similarity1" TYPE="number" LENGTH="5" NOTNULL="false" UNSIGNED="true" SEQUENCE="false" DECIMALS="2" COMMENT="similarity rate of student id 1" PREVIOUS="additional_codefile_name" NEXT="similarity2"/> <FIELD NAME="similarity2" TYPE="number" LENGTH="5" NOTNULL="false" UNSIGNED="true" SEQUENCE="false" DECIMALS="2" COMMENT="similarity rate of student id 2" PREVIOUS="similarity1" NEXT="comparison"/> <FIELD NAME="comparison" TYPE="char" LENGTH="100" NOTNULL="false" SEQUENCE="false" PREVIOUS="similarity2" NEXT="comments"/> <FIELD NAME="comments" TYPE="char" LENGTH="150" NOTNULL="false" SEQUENCE="false" PREVIOUS="comparison" NEXT="reportid"/> <FIELD NAME="reportid" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" COMMENT="foreign key to table programming_report" PREVIOUS="comments" NEXT="mark"/> <FIELD NAME="mark" TYPE="char" LENGTH="1" NOTNULL="false" SEQUENCE="false" COMMENT="used to mark a pair of assignment as having unusually high similarities" PREVIOUS="reportid"/> </FIELDS> <KEYS> <KEY NAME="pk_programming_result_id" TYPE="primary" FIELDS="id"/> </KEYS> <INDEXES> <INDEX NAME="repst1st2_index" UNIQUE="false" FIELDS="reportid, student1_id, student2_id" COMMENT="Index for reportid, student1_id, student2_id for fast searching"/> </INDEXES> </TABLE> <TABLE NAME="plagiarism_programming_cours" COMMENT="containing the courses which is enabled when level is set to global" PREVIOUS="plagiarism_programming_reslt" NEXT="plagiarism_programming_date"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="course"/> <FIELD NAME="course" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="id"/> </FIELDS> <KEYS> <KEY NAME="id" TYPE="primary" FIELDS="id" COMMENT="Primary key"/> </KEYS> <INDEXES> <INDEX NAME="course_index" UNIQUE="false" FIELDS="course" COMMENT="Index for course for fast searching"/> </INDEXES> </TABLE> <TABLE NAME="plagiarism_programming_date" COMMENT="containing the scheduled scan date of each enabled assignment. One assignment can have several scan date" PREVIOUS="plagiarism_programming_cours"> <FIELDS> <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="scan_date"/> <FIELD NAME="scan_date" TYPE="int" LENGTH="20" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="id" NEXT="finished"/> <FIELD NAME="finished" TYPE="int" LENGTH="1" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="scan_date" NEXT="settingid"/> <FIELD NAME="settingid" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="false" SEQUENCE="false" PREVIOUS="finished"/> </FIELDS> <KEYS> <KEY NAME="date_primary" TYPE="primary" FIELDS="id" COMMENT="primary key"/> </KEYS> <INDEXES> <INDEX NAME="setfinsca_index" UNIQUE="false" FIELDS="settingid, finished, scan_date" COMMENT="Index for settingid, finished, scan_date for fast searching"/> </INDEXES> </TABLE> </TABLES> </XMLDB> Hello, I want to export data from php-mysql to excel in a particular cell. 1. Get the data from php-Mysql and export data to excel sheet. I already create a template excel sheet format.I want to export data from mysql to a particular cell. Sorry I can't able to attach my template excel sheet. 2. Is it possible to export image? Thanks in advance Hello, Ive got a mysql database but i used a script to add a bunch of file names but it entered with a few errors and theres quite a few that got entered 4-5 times. I can get all the names fine using "SELECT DISTINCT name FROM games" but how can i export all the other fields not just the names. I want to select everything but only DISTINCT on the names I have the following query "select distinct article_author, article_author2 from articles" When I run this it returns two columns. However is there a way in which I can group these two columns into 1 column? Thanks for any help. Hi everyone, I am working on a website that involves MySQL. Here is the example to my table. field1 | field2 George Fritz | www.fritz.org Linda Gomez | www.gomez.org I need a code, that will fetch field2 of the given field1. For example, if $field1 is George Fritz, the code should return www.fritz.org Any help? Thanks... Funstein I have text with an apostrophe (Bill's car) that has been encrypted and decrypted, then inserted into a database. After it is decrypted but before inserting it, I process it with $field1 = mysqli_real_escape_string($cxnA, $field1) When I insert the field and view it in MySQL Query Browser, it shows the converted apostrophe: Bill's car When I select on the field Code: [Select] select * from table1 where field1='Bill\'s car'this returns no records I have to use Code: [Select] select * from table1 where field1='Bill's car'to get the record, which is very inconvenient If I display this field in a browser using <input type='text' name=field1 value='".htmlentities($field1Value, ENT_QUOTES, 'UTF-8')."' maxlength='50'> I get - Bill's car - in the browser, which is not what I want. I have to use <input type='text' name=field1 value='".htmlspecialchars_decode(htmlentities($field1Value, ENT_QUOTES, 'UTF-8'), ENT_QUOTES)."' maxlength='50'> to get Bill's car to display. The strange thing is that when I echo field1 before it is inserted, it shows Bill's car. I have even tried the following to get it to insert the proper value: $field1 = htmlspecialchars_decode(mysqli_real_escape_string($cxnA, $field1), ENT_QUOTES); but this doesn't work either. How do you prevent this from happening? Thanks. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=320026.0 I am trying to simplify my coding work for my website and wanting to make it operate more professionally (as part of my training to better my programming skills, which I know will take a lot of time to do). One of the things I`d like to improve on it is to create tables and fields in my mysql database, but not have to go into phpmyadmin to do it. I know open source programs like Drupal can do this easily so that when you add modules, the user does not have to access the database to set it up. But looking at the codes, I`m unable to figure out how it works. How will I be able to program my code to do something like, I`ll have a form where I enter in what I want the new table to be called, what fields to add in and their specifications, where I could do this by just going into my website as the admin? I have the following PHP script to update two time/date fields in the database. When i run this the fields are not updated. Can anyone see where i m going wrong. <?php $con = mysql_connect("localhost","dbname","dbpassword"); if (!$con) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully'; mysql_select_db("my_db", $con); mysql_query("UPDATE msm_content SET created = '2011-01-02 00:00:00', modified = '2011-01-01 00:00:00'"); echo 'Query Updated successfully'; mysql_close($con); ?> Your guidance is much appreciated. I have problem that only last text field is updated, how should I fix this? Here's the code <?php if(isset($_POST["update"])){ mysql_query("UPDATE categories SET name_category = '".$_POST['category']."' WHERE ID= ".$_POST['currentCat']." ") or die(mysql_error()); mysql_query("UPDATE podkategorije SET name_subcategory = '".$_POST['subcategory']."' WHERE id_subCat= ".$_POST['currentSubCat']." ") or die(mysql_error()); } ?> <form action="" method="post" > <?php //creating texfields from db $query = "SELECT k.ID, k.name_category, pk.name_subcategory, pk.id_subCat FROM `categories` AS k JOIN `subcategories` AS pk ON pk.id_mainCat = k.ID"; $result = mysql_query($query) or die(mysql_error()); $currentCat = false; while($row = mysql_fetch_array($result)) { //so it doesn't repeat itself if($currentCat != $row['ID']) { //display of main Categories ?> <ul> <li> <br/><input name="categories" type="text" value="<?php echo $row['name_category']; ?>" /> </li> </ul> <? $currentCat = $row['ID']; } //display subcategories ?> <input name="subcategories" type="text" value="<?php echo $row['name_category']; ?>" /><br/> <input type="hidden" name="currentCat" value="<?php echo $row['ID']; ?>" /> <input type="hidden" name="currentSubCat" value="<?php echo $row['id_subCat']; ?>" /> <? } ?> <br /> <input type="button" value="Back" onClick="history.go(-1);return true;"> <input type="submit" value="Update" name="update"/> </form> Hi Guys, I have stuck with my problem and i am nothing to php, i already posted this to another php script forum, but haven't solve, so i wondering if anyone here help me and many thanks. this is all about game scores from .xml file inside the xml file itself as: Code: [Select] <gesmes:Envelope> <gesmes:subject>Reference Scores</gesmes:subject> - <gesmes:Sender> <gesmes:name>Game Information Scores</gesmes:name> </gesmes:Sender> - <Cube> - <Cube time="2010-10-13"> <Cube scores="GameA1" value="1.5803"/> <Cube scores="GameA2" value="21.35"/> ............etc <Cube scores="GameA15" value="135"/> </Cube> </Cube> </gesmes:Envelope> then i got php script that can save all data of .xml above to mysql, look like <?php class Scores_Converter { var $xml_file = "http://192.168.1.112/gamescores/scores-daily.xml"; var $mysql_host, $mysql_user, $mysql_pass, $mysql_db, $mysql_table; var $scores_values = array(); //Load convertion scores function Scores_Converter($host,$user,$pass,$db,$tb) { $this->mysql_host = $host; $this->mysql_user = $user; $this->mysql_pass = $pass; $this->mysql_db = $db; $this->mysql_table = $tb; $this->checkLastUpdated(); $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); $rs = mysql_select_db($this->mysql_db,$conn); $sql = "SELECT * FROM ".$this->mysql_table; $rs = mysql_query($sql,$conn); while($row = mysql_fetch_array($rs)) { $this->scores_values[$row['scores']] = $row['value']; } } /* Perform the actual conversion, defaults to 1.00 GameA1 to GameA3 */ function convert($amount=1,$from="GameA1",$to="GameA3",$decimals=2) { return(number_format(($amount/$this->scores_values[$from])*$this->scores_values[$to],$decimals)); } /* Check to see how long since the data was last updated */ function checkLastUpdated() { $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); $rs = mysql_select_db($this->mysql_db,$conn); $sql = "SHOW TABLE STATUS FROM ".$this->mysql_db." LIKE '".$this->mysql_table."'"; $rs = mysql_query($sql,$conn); if(mysql_num_rows($rs) == 0 ) { $this->createTable(); } else { $row = mysql_fetch_array($rs); if(time() > (strtotime($row["Update_time"])+(12*60*60)) ) { $this->downloadValueScores(); } } } /* Download xml file, extract exchange values and store values in database */ function downloadValueScores() { $scores_domain = substr($this->xml_file,0,strpos($this->xml_file,"/")); $scores_file = substr($this->xml_file,strpos($this->xml_file,"/")); $fp = @fsockopen($scores_domain, 80, $errno, $errstr, 10); if($fp) { $out = "GET ".$scores_file." HTTP/1.1\r\n"; $out .= "Host: ".$scores_domain."\r\n"; $out .= "User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8) Gecko/20051111 Firefox/1.5\r\n"; $out .= "Connection: Close\r\n\r\n"; fwrite($fp, $out); while (!feof($fp)) { $buffer .= fgets($fp, 128); } fclose($fp); $pattern = "{<Cube\s*scores='(\w*)'\s*value='([\d\.]*)'/>}is"; preg_match_all($pattern,$buffer,$xml_values); array_shift($xml_values); for($i=0;$i<count($xml_values[0]);$i++) { $exchange_value[$xml_values[0][$i]] = $xml_values[1][$i]; } $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); $rs = mysql_select_db($this->mysql_db,$conn); foreach($exchange_value as $scores=>$value) { if((is_numeric($value)) && ($value != 0)) { $sql = "SELECT * FROM ".$this->mysql_table." WHERE scores='".$scores."'"; $rs = mysql_query($sql,$conn) or die(mysql_error()); if(mysql_num_rows($rs) > 0) { $sql = "UPDATE ".$this->mysql_table." SET value=".$value." WHERE scores='".$scores."'"; } else { $sql = "INSERT INTO ".$this->mysql_table." VALUES('".$scores."',".$value.")"; } $rs = mysql_query($sql,$conn) or die(mysql_error()); } } } } /* Create the scores table */ function createTable() { $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); $rs = mysql_select_db($this->mysql_db,$conn); $sql = "CREATE TABLE ".$this->mysql_table." ( scores char(3) NOT NULL default '', value float NOT NULL default '0', PRIMARY KEY(scores) ) ENGINE=MyISAM"; $rs = mysql_query($sql,$conn) or die(mysql_error()); $sql = "INSERT INTO ".$this->mysql_table." VALUES('GameA0',1)"; $rs = mysql_query($sql,$conn) or die(mysql_error()); $this->downloadValueScores(); } } ?> but that php script above just create table of mysql below CREATE TABLE IF NOT EXISTS `scrore_table` ( `scores` char(3) NOT NULL default '', `value` float NOT NULL default '0', PRIMARY KEY (`scores`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `scrore_table` (`scores`, `value`) VALUES ('GameA0', 1), ('GameA1', 1.5651), ......etc ('GameA15', 95.572); while of my existing database table look like: CREATE TABLE IF NOT EXISTS `scrore_table` ( `scrore_id` int(11) NOT NULL auto_increment, `scrore_title` varchar(32) collate utf8_bin NOT NULL default '', `scores` varchar(3) collate utf8_bin NOT NULL default '', `decimal_place` char(1) collate utf8_bin NOT NULL, `value` float(15,8) NOT NULL, `date_updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`currency_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ; INSERT INTO `scrore_table` (`scrore_id`, `scrore_title`, `scores`, `decimal_place`, `value`, `date_updated`) VALUES (1, 'Game Class A0', 'GameA0', '2', 1.00000000, '2010-04-06 22:00:54'), (2, 'Game Class A1', 'GameA1', '2', 1.52600002, '2010-04-06 22:00:54'), ..............................etc (14, 'Game Class A15', 'GameA15', '2', 1.13999999, '2010-04-06 22:00:54'); as i said i newbie to php then i dont know how to modify the php code above able to automatically create the table and insert/update new fields e.g. scrore_id, scrore_title,decimal_place, date_updated also all values to my existing database i looking for some helps and thanks in advance.. Now, here's what I'm trying to do:
First, I have a file filled with data like such:
title
tag
1
2
Title
Description
Etc
Next, I upload that file to my site which then proceeds to make an array with said data and then inserts it into my database. But this is not the intended behavior. Right now, if I upload the same file again, it will re-insert everything and duplicate all entries.
What I want to do is check if the data in the file has already been added, do nothing. If it's been modified, I want to update the database where changes have been made and not duplicate anything.
Currently, my code does all that except one thing whre I'm really stuck: it won't update the changes from the file to the database. I've tried echoing everything and it's to be working except for the query so I take it the error is in there but I can't find it... I'm still learning PHP and MySQL so I thought maybe somebody could help indicate where or what I'm doing wrong in the query. Thanks in advance !
Here's my attempt at doing so:
$register_ep_data = array(
'show' => $name,
'season' => $srNum,
'ep' => $epNum,
'app_name' => $epName,
'tag' => $tag,
'app_about' => $desc,
'app_website' => $imdb,
'app_release' => $release,
'type' => $type,
'app_code' => $Frame
);
array_walk($register_ep_data, 'array_fu');
$fields = '`' . implode('`, `', array_keys($register_ep_data)) . '`';
$data = '\'' . implode('\', \'', $register_ep_data) . '\'';
$epFound = false;
$id = 0;
while ($ep_list_data = mysql_fetch_array($turtle)) {
$id = $ep_list_data['app_id'];
$currentNAME = $ep_list_data['app_name'];
$SERIES = $ep_list_data['show'];
if ($SERIES == $name) {
if ($currentNAME == $epName) {
$epFound = true;
break;
}
}
}
if ($epFound) {
mysql_query("UPDATE `games` SET ($fields) VALUES ($data) WHERE `app_id` = '$id'");
} else {
mysql_query("INSERT INTO `games` ($fields) VALUES ($data)");
}
A few explanations:
$fields would equal to something like: `show`, `season`, `ep`, etc...
and $data to 'example', '1', '2', 'etc'
I want to have a search product feature, but I would like members to be able to search multiple fields in one go i.e. product_code, Product_name in one MySQL query. The thing is, members have to be logged on, so the query must also only show results relating to that specific member, via the session[member_ID], my current query for listing products for that specific member is : Code: [Select] $sql = "SELECT productId, productCode, image, name, price, stock_level FROM product_inventory WHERE memberr_ID = '" . $_SESSION['SESS_mem_ID'] . "; How would I change the above into a search query to search for productcode, productname and still only show results beloging to this member using the session data ? all help appreciated I can not get the values from the javascript add row to go dynamically as a row into MySql only the form values show up as the form below as one row. I made it as an array, but no such luck, I have tried this code around a multitude of ways. I don't know what I am doing wrong, kindly write out the correct way.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR...ransitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Dynamic Fields js/php to MySql need to submit dynamically to the database</title> <?php require ('database.php'); ?> <script type="text/javascript"> var counter = 1; var collector = ""; function addfields(indx) { var tbl = document.getElementById('table_id'); var newtr = document.createElement('tr'); counter = counter + indx; newtr.setAttribute('id','tr'+counter); newtr.innerHTML = '<td><input type="checkbox" name="checkb'+counter+'" id="checkb'+counter+'" value="'+counter+'" onclick="checkme('+counter+')"></td><td><input type="text" name="text1[]"></td><td><textarea name="textarea1[]"></textarea></td>'; tbl.appendChild(newtr); } function checkme(dx) { collector += dx+","; } function deletetherow(indx) { var col = collector.split(","); for (var i = 0; i < col.length; i++) { var remvelem = document.getElementById('tr'+col[i]); var chckbx = document.getElementById("checkb"+col[i]); if(remvelem && chckbx.checked) { var tbl = document.getElementById('table_id'); tbl.removeChild(remvelem); } } } </script> </head> <body> <form enctype="multipart/form-data" id="1" style="background-color:#ffffff;" action="<?php echo $_SERVER['PHP_SELF']; ?>"></form> <table id="table_id" > <tr id="tr1" class="trmain"> <td> </td> <td> <input type="text" name="text1[]"> </td> <td> <textarea name="textarea1[]"></textarea> </td> </tr> </table> <input type="button" value="Add" onClick="addfields(1);" /> <input type="button" value="Delete" onClick="deletetherow()" /> <input type="submit" value="Send" id="submit" name="submit"/> <?php if(isset($_POST['submit'])) { for ($i=0; $i < count($_POST['text1']); $i++ ) { $ced = stripslashes($_POST['text1'][$i]); $erg = stripslashes($_POST['textarea1'][$i]); } $bnt = mysql_query("INSERT INTO tablename (first, second) VALUES ('$ced', '$erg')")or die('Error: '. mysql_error() ); $result = mysql_query($bnt); } ?> </body> </html> I'm using PHP and MySQL. I have an application where I'd like to keep certain database (character) fields encrypted when added to the database and stored, but when I need to display them at the appropriate time, I can call a function and quickly decrypt them for display. Does anyone know of a simple way to do this? Thanks! hi, i'm new to php. i've got a problem. I've 3 tables and table has following content table 1 id name email address phone execution_date executor_name web_address table 2 id name email address phone execution_date executor_name project_title table 3 id name email address phone execution_date executor_name reviewer I want to export these table to and spreadsheet (.xls) with some criteria a form will assign which data will published at the spreadsheet with these criteria #all data can be exported from three tables #some column can be selected from three tables #some or all data can be selected from individual table i've implement a script for this but it didn't meet my requirements. Can anyone help? here is my script <?php $DB_Server = "localhost"; //your MySQL Server $DB_Username = "root"; //your MySQL User Name $DB_Password = "pass"; //your MySQL Password $DB_DBName = "mydb"; //your MySQL Database Name $search_from_date = $_POST['start_date']; $search_to_date = $_POST['end_date']; $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); $Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); $now_date = date('m-d-Y H:i'); if(($_POST['typer_of_report'] == 'rrc_report') || ($_POST['typer_of_report'] == 'all_report')) { $DB_TBLName = 'rrc_record'; } if(($_POST['typer_of_report'] == 'erc_report') || ($_POST['typer_of_report'] == 'all_report')) { $DB_TBLName2 = 'erc_record'; } if(($_POST['typer_of_report'] == 'aeec_report') || ($_POST['typer_of_report'] == 'all_report')) { $DB_TBLName3 = 'aeec_record'; } $file_type = "vnd.ms-excel"; $file_ending = "xls"; //} header("Content-Type: application/$file_type"); header("Content-Disposition: attachment; filename=protocol_report.$file_ending"); header("Pragma: no-cache"); header("Expires: 0"); if($DB_TBLName) { $sql = "Select * from ".$DB_TBLName." where execution_date >= '".$search_from_date."' and execution_date <= '".$search_to_date."' order by execution_date desc"; $Use_Title = 1; $title = "Report for $DB_TBLName on $now_date"; $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); if ($Use_Title == 1) { echo("$title\n"); } $sep = "\t"; //tabbed character for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } //} echo "\n...\n"; } if($DB_TBLName2) { $sql = "Select * from ".$DB_TBLName2." where execution_date >= '".$search_from_date."' and execution_date <= '".$search_to_date."' order by execution_date desc"; $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); $Use_Title = 1; $title = "Report for $DB_TBLName2 on $now_date"; if ($Use_Title == 1) { echo("$title\n"); } $sep = "\t"; //tabbed character for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } echo "\n...\n"; } if($DB_TBLName3) { $sql = "Select * from ".$DB_TBLName3." where execution_date >= '".$search_from_date."' and execution_date <= '".$search_to_date."' order by execution_date desc"; $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); $Use_Title = 1; $title = "Report for $DB_TBLName3 on $now_date"; if ($Use_Title == 1) { echo("$title\n"); } $sep = "\t"; //tabbed character for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } } ?> have anyone any idea? In brief, I'm attempting to capture the form data from a dynamic form to a mysql database. From he
To He
The post data looks like this from the form - Array(
... ) PHP CODE:
... As a non-php or mysql developer, I'm learning on the fly. This is a section of the php file that I was using to post the form data to mySQL. This worked great up to the point I added the dynamic form widget.
<?php
// This function will run within each post array including multi-dimensional arrays
function ExtendedAddslash(&$params)
{
foreach ($params as &$var) {
// check if $var is an array. If yes, it will start another ExtendedAddslash() function to loop to each key inside.
is_array($var) ? ExtendedAddslash($var) : $var=addslashes($var);
unset($var);
}
}
// Initialize ExtendedAddslash() function for every $_POST variable
ExtendedAddslash($_POST);
$submission_id = $_POST['submission_id'];
$formID =$_POST['formID'];
$ip =$_POST['ip'];
$fname =$_POST['fname'];
$lname =$_POST['lname'];
$spousename =$_POST['spousename'];
$address =$_POST['address'][0]." ".$_POST['address'][1]." ".$_POST['address'][2]." ".$_POST['address'][3]." ".$_POST['address'][4]." ".$_POST['address'][5];
...
$db_host = 'localhost';
$db_username = 'xxxxx';
$db_password = 'xxxxx';
$db_name = 'xxxxx';
mysql_connect( $db_host, $db_username, $db_password) or die(mysql_error());
mysql_select_db($db_name);
// search submission ID
$query = "SELECT * FROM `tableName` WHERE `submission_id` = '$submission_id'";
$sqlsearch = mysql_query($query);
$resultcount = mysql_numrows($sqlsearch);
if ($resultcount > 0) {
mysql_query("UPDATE `tableName` SET
`fname` = '$fname',
`lname` = '$lname',
`spousename` = '$spousename',
`address` = '$address',
WHERE `submission_id` = '$submission_id'")
or die(mysql_error());
} else {
mysql_query("INSERT INTO `tableName` (submission_id, formID, IP, fname, lname, spousename, address)
VALUES ('$submission_id', '$formID', '$ip', '$fname', '$lname', '$spousename', '$address') ")
or die(mysql_error());
}
?>
It has been suggested that I explore using the PHP Explode() function. It's possible that may work, but can't get my head around how to apply the function to the PETLIST Array. Looking for suggestions or direction to find a solution for this challenge. Looking forward to any replies. |
|||||||