|
PHP - Creating New Tables And Fields In Mysql Without Going To Phpmyadmin?
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? Similar Tutorialswhat is dificult in creating this tables... CREATE TABLE `pois` ( `id` bigint(20) unsigned NOT NULL, `lat` float(10,7) NOT NULL, `lon` float(10,7) NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) )i get back the following error ; MySQL meldet: Dokumentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname`' at line 8many thanks for any and all help Ok, so I've spent quite a bit of time piecing together this solution from a variety of sources. As such, I may have something in my code below that doesn't make sense or isn't neccessary. Please let me know if that is the case. I'm creating an administrative form that users will you to add/remove items from a MySQL table that lists open positions for a facility. The foreach loop generates all of the possible job specialties from a table called 'specialty_list'. This table is joined to a second table ('open_positions') that lists any positions that have been selected previously. Where I'm stuck is getting the checkbox to be checked if the facility_ID from the open_positions table matches the $id passed in the URL via ?facility_id=''. Here's where I am so far: $query = "SELECT specialty_list.specialty_displayname , specialty_shortname , open_positions.position , facility_ID FROM specialty_list LEFT OUTER JOIN open_positions ON open_positions.position = specialty_list.specialty_shortname ORDER BY specialty_list.specialty_shortname"; $results = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($results)) { $positions[$row['specialty_shortname']] = $row['specialty_displayname']; } echo "<form method='POST' action='checkbox.php'>"; foreach($positions as $specialty_shortname => $specialty_displayname) { $facility_ID = $row['facility_ID']; $checked = $facility_ID == $row['facility_ID'] ? ' checked' : ''; echo "<input type='checkbox' name='position[]' value=\"{$specialty_shortname}\"{$checked}> {$specialty_displayname}</input><br/>"; } echo "<input type='hidden' name='facility_ID' value='$id'>"; echo "<input type='submit' value='Submit Checkboxes!'>"; echo "</form>"; Any ideas how to get this working? I feel like I'm very close, but I just can't get it. I also tried starting from scratch with a WHILE statement instead of a FOREACH, but haven't tweaked it enough to prevent duplicate checkboxes. With that in mind, here it is, just in case that's a better direction: $query = "SELECT specialty_list.specialty_displayname , specialty_shortname , open_positions.position , facility_ID FROM specialty_list LEFT OUTER JOIN open_positions ON open_positions.position = specialty_list.specialty_shortname ORDER BY specialty_list.specialty_shortname"; $results = mysql_query($query) or die(mysql_error()); echo "<form method='POST' action='checkbox.php'>"; while($row=mysql_fetch_assoc($results)) { $facility_ID = $row['facility_ID']; $specialty_shortname = $row['specialty_shortname']; $specialty_displayname = $row['specialty_displayname']; if ($facililty_ID==$id) { $checked=' checked'; } echo "<input type='checkbox' name='position[]' value=\"$specialty_shortname\"$checked> $specialty_displayname</input><br/>"; } echo "<input type='hidden' name='facility_ID' value='$id'>"; echo "<input type='submit' value='Submit Checkboxes!'>"; echo "</form>"; I have a table leitner_vcard_boxes. Each row represents a flashcard. I want to be able to efficiently sort the cards by the frequency with which the user got them correct. Each row has a column right_count and wrong_count and a percent_correct which I calculate in php as right_count/(right_count+wrong_count) I want to replace the manual calculation with a trigger since I believe that would be more efficient than reading the row, incrementing the right or wrong counter, calculating the new percentage correct and updating the row. The trigger I plan to use is:
CREATE TRIGGER upd_right_percent AFTER UPDATE ON leitner_vcard_boxes
FOR EACH ROW
BEGIN
UPDATE leitner_vcard_boxes SET right_percent=right_count/(right_count+wrong_count);
END;
My questions a
1. Is the trigger going to give me the desired outcome?
2. If I export the leitner_vcard_boxes table from my development server's phpMyAdmin and replace (import) the table on the test server will the trigger move with it? Do I need to move the trigger separately?
3. How do I list the triggers in the database? Can I see them in phpMyAdmin someplace and I am just missing it?
4. What else should somebody who has not used triggers before consider?
I have 5 tables that I changed on my development server (laptop) that I'd like to upload to my test server. I can export just those tables from PhpMyAdmin but on import it drops all of the other tables first. Short of editing the SQL before I upload it (finding the drop and deleting it) is there an easy way to uncheck a box and change that behavior that I am just not seeing?
Please Help me on how to create a website using with code n a little bit explanation using PHP n phpmyadmin. The mainthing that i need to do is using php create a simple site that logs on by connecting it to the phpmyadmin database such that i need to store the user details like name,mobile number,age,loaction,lattitude n longitude in database.Also i need to retrieve the database whenever required!!! Can Any1 please help how to do this...It means a lot to me n i'm a new User n Beginner to learn these things!!! Thank u for reading this Im doing a search system and Im having some problems.
I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do.
I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment.
But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table.
I need to acess in my news table this fields: id, title, content, link, date, nViews
I need to acess in my pages table this fields: id, title, content, link
Im trying to do this also with UNION, but in this case Im not having any row returning.
Do you see what I have wrong in my code?
<?php //first I get my $search keyword $search = $url[1]; $pdo = connecting(); //then I want to show number of returned rows for keyword searched $readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ? UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?"); $readALL->bindValue(1,"%$search%", PDO::PARAM_STR); $readALL->bindValue(2,"%$search%", PDO::PARAM_STR); $readALL->bindValue(3,"%$search%", PDO::PARAM_STR); $readALL->bindValue(4,"%$search%", PDO::PARAM_STR); $readALL->execute(); //I show number of returned rows echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>'; //If dont return any rows I show a error message if($readALL->rowCount() <=0){ echo 'Sorry but we didnt found any result for your keyword search.'; } else{ //If return rows I want to show, if it is a page result I want to show title and link that I have in my page table //if it is a news result I want to show title and link that I have in my news table and also date of news echo '<ul class="searchlist">'; $readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4 UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4"); $readALL2->bindValue(1, '1'); $readALL2->bindValue(2, "%$search%", PDO::PARAM_STR); $readALL2->bindValue(3, "%$search%", PDO::PARAM_STR); $readALL2->bindValue(4, "%$search%", PDO::PARAM_STR); $readALL2->execute(); while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){ echo '<li>'; echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>'; echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>'; //if it is a news result I also want to show date on my list //echo '<span id="date">'.$result['date'].'</span>'; echo '</li>'; } echo ' </ul>'; //but how can I do my select statement to have access to my news table fields and my page table fields?? } ?> I have a db.php and inside it I filled out all the info needed:
<?php
define('DB_HOST', 'example.com');
define('DB_NAME', 'database_name');
define('DB_USERNAME', 'user_name');
define('DB_PASSWORD', '*******');
$odb = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
?>
My problem is with the DB_HOST, is that my direct URL to the site? for example, google.com or is it like an IP?
I have a really weird problem. I'm trying to run a mysql query that works fine in phpmyadmin but in php script is giving me an error. The query is: Code: [Select] (SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('WORD*' IN BOOLEAN MODE) AS score FROM articles art,galeries gal WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE)) UNION (SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE) AS score FROM articles WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT 0,30 Snipset from php script code: function search($start_row,$ammount,$search_key,$pages){ $start_row = intval($start_row) * $ammount; $return_val = ""; $symbols = array('/','\\','\'','"',',','.','<','>','?',';',':','[',']','{','}','|','=','+','-','_',')','(','*','&','^','%','$','#','@','!','~','`' );//this will remove punctuation $pattern = "#[^(\w|α|β|γ|δ|ε|ζ|η|θ|ι|κ|λ|μ|ν|ξ|ο|π|ρ|σ|τ|υ|φ|χ|ψ|ω|Α|Β|Γ|Δ|Ε|Ζ|Η|Θ|Ι|Κ|Λ|Μ|Ν|Ξ|Ο|Π|Ρ|Σ|Τ|Υ|Φ|Χ|Ψ|Ω|ς|ά|έ|ό|ί|ύ|ώ|ή|ϊ|ϋ|ΐ|ΰ|Ά|Έ|Ό|Ί|Ύ|Ώ|Ή|Ϊ|Ϋ|\d|\'|\"|\.|\!|\?|;|,|\\|\/|\-|:|\&|@)]+#"; $search_key = greek_text::to_upper($search_key); $wc = strip_tags($search_key); $wc = preg_replace($pattern, " ", $wc); for ($i = 0; $i < sizeof($symbols); $i++) { $wc = str_replace($symbols[$i],' ',$wc); } $wc = str_replace("΄", " ", $wc); $wc = str_replace(chr(162), " ", $wc); if( !$keep_numbers ) { $wc = preg_replace('#(^|\s+)[\d\s]+(\s+|$)#',' ',$wc); $pattern = '#(^|\s+)([0-9]+[a-zA-ZαβγδεζηθικλμνξοπρστυφχψωΑΒΓΔΕΖΗΘΙΚΛΜΝΞΟΠΡΣΤΥΦΧΨΩςάέόίύώήϊϋΐΰΆΈΌΊΎΏΉΪΫ]+\s*)+(\s+|$)#'; preg_match($pattern,$wc,$aa); $wc = preg_replace($pattern, " ", $wc); } $wc = trim(preg_replace("/\s\s+/", " ", $wc)); $wc = explode(" ", $wc); $cleaned_keyword = array_filter($wc); $cleaned_keyword = greek_text::removeStopWordsFromArray($cleaned_keyword); $stemmed_keywords = greek_text::stemWordsArray($cleaned_keyword); $query = "(SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('"; while(list($key,$val)=each($stemmed_keywords)){ if($val<>" " and strlen($val) > 1){ $query .= $val."*"; $search_keys .= $val."* "; } } $query .= "' IN BOOLEAN MODE) AS score FROM articles art,galeries gal WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))"; $query .= " UNION (SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE) AS score FROM articles WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT ".intval($start_row).",".$ammount; $rs = $this->dbActions->execQuery($query); $this->dbActions->execQuery("INSERT INTO searches (`KEY`,`DATE`,`RESULTS`) VALUES ('".$search_key."',NOW(),".$pages.")"); $search_results = "<div id='results'>"; while($row = mysql_fetch_array($rs)){ $search_results.= "<div id='result'>"; $search_results.= "<div class='result_title'><h4><a href='article.php?articleId=".$row["ARTICLE_ID"]."'>".$row['TITLE']."</a></h4></div>"; $search_results.= "<div class='result_description'>".$row['DESCRIPTION']."</div>"; $search_results.= "</div>"; $search_results.= "<div class='result_seperator'></div>"; } $search_results .= "</div>"; $return_val = $search_results; return $return_val; } dbactions class: require 'includes/errors.php'; error_reporting(0); class DBActions{ var $dbCon; var $errorHandler; function DBActions(){ $this->dbCon = $this->dbCon(); $errorHandler = new errors(1); } private function dbCon(){ require 'conf/configuration.php'; $dbcon = mysql_connect($dbUrl,$dbUser,$dbPass); if(!$dbcon)trigger_error("Unable to connect to database $dbUrl for user $dbUser",E_USER_ERROR); mysql_select_db($dbName); mysql_query("SET NAMES 'UTF8'"); mysql_query('set character set utf8'); return $dbcon; } function dbClose(){ if($this->dbCon) mysql_close($this->dbCon); } function execQuery($query){ $result = mysql_query($query,$this->dbCon); $msg = "Unable to execute query ".$query; if(mysql_num_rows($result) > 0) return $result; else if($result == false) trigger_error($msg,E_USER_ERROR); else return $result; } function send_error_mail(){ $this->errorHandler->sendErrorEmail(); } } I've try everything but can't get this working.. Any healp whould be really appreciated. Thanks in advance. Hi, I have a sql query that executes perfectly in phpMyAdmin but when applied to a PHP script returns nothing. It won't even start a "while" loop. Please review code snippits below. Submission Script calls function in Database Class: $result = $database->testReturnId($_POST['years'], $_POST['model']); while($row = mysql_fetch_array($result)) { $i = $i + 1; echo "ROW " . $i . " " . $row['auto_id'] . " " . $row['auto_year'] . " " . $row['auto_year_high'] . " " . $row['auto_make'] . " " . $row['auto_model'] . "<br />"; } Database Class Function: function testReturnId($year, $model){ global $form; $q = 'SELECT * FROM tbl_svc_auto WHERE auto_model = "$model" AND "$year" BETWEEN auto_year AND auto_year_high'; if(!mysql_query($q, $this->connection)){ return false; }else{ return $result = mysql_query($q, $this->connection); } } I have moved the query to the submission script and replaced the variables with actual data and it still will not work. I can take the same query with out the variables and input it into phpMyAdmin and it works great. On the submission script, I added a row number to the echo thinking that it would a least return the word "ROW" and it does not. I have tested each step of the execution of the query and have not returned any errors. I do not receive any errors at all even during execution. It just seems to refuse to run the "while" statement. I do not know where my problem is......Please help. Thank you in advance for any assitance you can offer. Joshua
Hi, this query runs fine when I run it from PHPMyAdmin: UPDATE `tran_term_taxonomy` SET `description` = (SELECT keyword from `good_keywords` ORDER BY RAND() LIMIT 1,1) WHERE `tran_term_taxonomy`.`taxonomy` = 'post_tag' AND `tran_term_taxonomy`.`description` = "" LIMIT 1 However, when I run the same query in a PHP file on my server, the page doesn't load at all. The message I get is: www.somesite.com is currently unable to handle this request. HTTP ERROR 500. This is my PHP code:
<?php
include("/database/connection/path/db_connect.php");
$result4 = mysqli_query($GLOBALS["___mysqli_ston"], "UPDATE `tran_term_taxonomy` SET `description` = (SELECT keyword from `good_keywords` ORDER BY RAND() LIMIT 1,1) WHERE `tran_term_taxonomy`.`taxonomy` = 'post_tag' AND `tran_term_taxonomy`.`description` = "" LIMIT 1");
echo $result4;
?>
So how do I make this query work please? Thanks for your guidance. i have two tables Loan Application table and Loans table. There is status option and date loan was applied section. After status has been chosen as Pending from the dropdown field and date chosen and loan application saved, it goes under the loans section. Now, admin has to go and approve the loan and pick the date the loan was approved under the loans section but date can be chosen for the approved date but the status cant be changed to approve because it shows pending. Please what can i do so when i select the date for approval , the status should change to approved automatically Hi can anybody help I need to export and download tables from a database into a excel sheet. I have this code and it works what I need is to export specific fields and not just the whole table can anyone help modifying the code to export certain fields within the table please? Here is the code... Code: [Select] <?php $host = 'localhost'; $user = 'user'; $pass = 'password'; $db = 'qdbname'; $table = 'tablename'; $file = 'export'; $link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error()); mysql_select_db($db) or die("Can not connect."); $result = mysql_query("SHOW COLUMNS FROM ".$table.""); $i = 0; if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_assoc($result)) { $csv_output .= $row['Field']."; "; $i++; } } $csv_output .= "\n"; $values = mysql_query("SELECT * FROM ".$table.""); while ($rowr = mysql_fetch_row($values)) { for ($j=0;$j<$i;$j++) { $csv_output .= $rowr[$j]."; "; } $csv_output .= "\n"; } $filename = $file."_".date("Y-m-d_H-i",time()); header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); header( "Content-disposition: filename=".$filename.".csv"); print $csv_output; exit; ?> This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization) users: id / username / password / realname / access_level users_access_level: access_level / access_name What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries. Example row for users: 1234 / tmac / password / tmac / 99 Example row for users_access_level: 99 / Admin Using the examples above, I would want the output to appear as such: Username: Access Name: Tmac Admin I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction. The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself. Thanks for reading! Hi Guys, I am attempting to create a program for a local auction house. I arrived at a stand still on a certain issue which is creating a sticky form. On the "enter auction details page" the user will be entering data as the auction is in session. 1. item Description 2. Item Price 3. Bidders Id 4. Qty Of course it would be repetitive to require the user to keep entering the same data for every bidder, so I want to stick the obvious fields which are "item description" & "Item Price" as these don't change until a new item comes up for sale. As of know I am processing the input on a separate page which records all input to the database with a redirect back to the "enter auction details page". I have tried many times to get the item description and item price fields to stick. These would need to change as the user moves onto the next item in which the description and price would change as well. Here is my current code for this part: Code: [Select] <?php session_start(); $field_itemDescription = ""; //iyem description, default as blank if (isset($_SESSION['itemDescription'])) $itemDescription = $_SESSION['itemDescription']; ?> <form action="record_trans.php" method="post"> <font face= "calibri" size= "4"> <table> <tr> <td><b>Item Description:</b></td> <td><input type= "text" name= "itemDescription" size= "30" value="<?php echo $itemDescription;?>"></td> </tr> <tr> <td><b>Item Price:</b></td> <td><input type= "text" name= "itemPrice" size= "5" value="<?php echo $itemPrice;?>"> </td> </tr> </tr> <td><b>Winning Bidders:</b></td> <td><input type="text" name= "bidderId" size= "5" /> </td> </tr> <tr> <td><b>How many deals?:</b></td> <td><input type="text" name= "itemQty" size= "3" value= "1" /></td> </tr> </table> <center><input type="submit" name="submit" value= "Save & Cont." " /></center> </form></font> I have a relational table call sales with prodcut_id and custmer_id tables. I also have a product and customer tables. products table with product_id as an auto increment and customer with custumer_id I want to join through the sales tables all t he fields of row 1 from tables customer and products and pull it at once. This is a member login script and I want to display the products by members. So far I have this query to display the products once the member is login in. $userid is the id of the customer coming from the $userid= $_SESSION['customer_id']; $mysqlSales="SELECT products.* FROM products JOIN sales ON (products.product_id = procuct_id ) WHERE sales.customer_id = '$userid'"; so far that statement is not working where should I have some type of incoherance with the english statement above expressing what I want the query to do. Here is the sample page: http://hoosierhoopsreport.com/pg-test/ Trying to create a table that has three sections (groupings). That part I have figured out. The first grouping is sorted numerically. The second group is sorted alphabetically based on what part of the state they live in. It's breaking down this second part that I can't seem to get. (The third group is just what's left over, sorted alphabetically.) So it needs to look like this: (Each group and region have their own header row. Only Group 2 is broken down into Regions.) Group 1 Group 2 -Region 1 -Region 2 -Region 3 -Region 4 -Region 5 Group 3 The sample page above, in Group 2, it just shows Region 1 header. Code: [Select] $query = 'SELECT * FROM a_playerRank WHERE year="2015" and position="1" ORDER BY grouping DESC,rankPos,region,nameLast'; $results = mysql_query($query) or trigger_error('MySQL error: ' . mysql_error()); $currentGrouping = false; $currentRegion = false; while($line = mysql_fetch_assoc($results)) { if($currentGrouping != $line['grouping']) { //Status has changed, display status header $currentGrouping = $line['grouping']; if($line['grouping']==2) { echo '<thead> <tr> <th class="num">#</th> <th class="top">Top 10</th> <th class="height">HT</th>'; if (current_user_can("access_s2member_level4")){ echo '<th class="level">Level</th>'; } echo' <th class="school">City (School)</th>'; if (current_user_can("access_s2member_level4")){ echo '<th class="summer">Summer Team</th>'; } echo' <th class="college">College</th> </tr> </thead>'; } if($line['grouping']==1) {echo '<tr><th colspan="7">Best of the Rest</th></tr>'; if($currentRegion != $line['region']) { $currentRegion = $line['region']; echo '<tr><th colspan="7">Region' .$line['region'] . '</th></tr>';} } if($line['grouping']==0) echo '<tr><th colspan="7">Names to Know</th></tr>'; } I am new to php and need help creating addition and multiplication tables using the html code we were given. I feel like I am on the right track, but I am getting a few errors and I cant figure them out. One of the errors is that it tells me I am not putting in a valid number for rows even though it is a positive number so it should work. Here is my code and all help is appreciated thanks in advance.
<html>
<head/>
<body>
<form method="POST" action="<?= $_SERVER['PHP_SELF'] ?>">
<table border="1">
<tr><td>Number of Rows:</td><td><input type="text" name="rows" /></td></tr>
<tr><td>Number of Columns:</td><td><select name="columns">
<option value="1">1</option>
<option value="2">2</option>
<option value="4">4</option>
<option value="8">8</option>
<option value="16">16</option>
</select>
</td></tr>
<tr><td>Operation:</td><td><input type="radio" name="operation" value="multiplication" checked="yes">Multiplication</input><br/>
<input type="radio" name="operation" value="addition">Addition</input>
</td></tr>
</tr><td colspan="2" align="center"><input type="submit" name="submit" value="Generate" /></td> </tr>
</table>
</form>
<?php
//check to see if num of rows is numberic
if (isset($_POST["rows"]) && is_numeric($_POST["rows"])){
//check to see if rows is a positive number
if($_POST["rows"] > 0){
if(isset($_POST) && $_POST['operation'] == "multiplication")
{
for($r = 0; $r < $_POST["rows"]; $r++){
echo '<tr>';
for($c = 0; $c < $_POST["columns"]; $c++){
echo '<td>' .$c*$r. '</td>';
echo '</tr>';
}}
}
else if (isset($_POST) && $_POST['operation'] == "addition")
{
for($r = 0; $r < $_POST["rows"]; $r++){
echo '<tr>';
for($c = 0; $c < $_POST["columns"]; $c++){
echo '<td>' .$c+$r. '</td>';
echo '</tr>';
}
}
}
}
else{
echo 'Invalid rows columns parameters';
exit();
}
}
else{
echo 'Invalid rows columns parameters';
exit();
}
?>
</body>
</html>
Hi, So i have HTML table which i'm attempting to turn into executable SQL code which i can then insert into a SQL database table. I'm wondering if anyone has any good ideas for the best way to get this done? I need to extract the data from each of the <tr bgcolor="#EEEEEE">, scrape the text within each <td></td> and then format it in sql. so far i have removed the html not required and created an array by exploding "<tr bgcolor="#EEEEEE">", however, its creating an empty element in the first array index [0], to fix this i have tried $players = array_shift($players); however it removed everything from the array. next i was thinking i would need to remove all html within each array element and add a comma to the end, then i would need to compress it all back together again? would this be the best way to do this? source html
<html>
<head>
<title><br/></title>
<style type ="text/css">
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
}
th {
padding: 5px;
text-align: left;
}
td {
padding: 2px;
border-style: solid;
border-width: 1px
}
</style>
</head>
<body>
<center><B><br/>
</center></B>
<p>
<table bordercolor="#000000" width="90%" align="center">
<tr bgcolor="#EEEEEE">
<th>UID</th>
<th>Name</th>
<th>Nat</th>
<th>Position</th>
<th>Best Pos</th>
<th>Age</th>
<th>DoB</th>
<th>Height</th>
<th>Weight</th>
<th>Expires</th>
<th>Wage</th>
<th>Value</th>
<th>Aer</th>
<th>Cmd</th>
<th>Com</th>
<th>Ecc</th>
<th>Han</th>
<th>Kic</th>
<th>1v1</th>
<th>Pun</th>
<th>Ref</th>
<th>TRO</th>
<th>Thr</th>
<th>Cor</th>
<th>Cro</th>
<th>Dri</th>
<th>Fin</th>
<th>Fir</th>
<th>Fre</th>
<th>Hea</th>
<th>Lon</th>
<th>L Th</th>
<th>Mar</th>
<th>Pas</th>
<th>Pen</th>
<th>Tck</th>
<th>Tec</th>
<th>Agg</th>
<th>Ant</th>
<th>Bra</th>
<th>Cmp</th>
<th>Cnt</th>
<th>Dec</th>
<th>Det</th>
<th>Fla</th>
<th>Ldr</th>
<th>OtB</th>
<th>Pos</th>
<th>Tea</th>
<th>Vis</th>
<th>Wor</th>
<th>Acc</th>
<th>Agi</th>
<th>Bal</th>
<th>Jum</th>
<th>Nat</th>
<th>Pac</th>
<th>Sta</th>
<th>Str</th>
</tr>
<tr bgcolor="#EEEEEE">
<td>859596</td>
<td>Tim Krul</td>
<td>NED</td>
<td>GK</td>
<td>GK</td>
<td>31</td>
<td>3/4/1988 (31 years old)</td>
<td>194 cm</td>
<td>83 kg</td>
<td>30/6/2022</td>
<td>£25,000 p/w</td>
<td>£12.75M</td>
<td>15</td>
<td>13</td>
<td>14</td>
<td>8</td>
<td>13</td>
<td>13</td>
<td>15</td>
<td>12</td>
<td>16</td>
<td>15</td>
<td>12</td>
<td>1</td>
<td>2</td>
<td>1</td>
<td>3</td>
<td>8</td>
<td>4</td>
<td>5</td>
<td>3</td>
<td>2</td>
<td>2</td>
<td>9</td>
<td>4</td>
<td>3</td>
<td>8</td>
<td>11</td>
<td>12</td>
<td>14</td>
<td>13</td>
<td>13</td>
<td>12</td>
<td>14</td>
<td>2</td>
<td>14</td>
<td>2</td>
<td>15</td>
<td>10</td>
<td>5</td>
<td>13</td>
<td>11</td>
<td>13</td>
<td>14</td>
<td>17</td>
<td>13</td>
<td>8</td>
<td>14</td>
<td>10</td>
</tr>
<tr bgcolor="#EEEEEE">
<td>5204730</td>
<td>Michael McGovern</td>
<td>NIR</td>
<td>GK</td>
<td>GK</td>
<td>34</td>
<td>12/7/1984 (34 years old)</td>
<td>188 cm</td>
<td>86 kg</td>
<td>30/6/2021</td>
<td>£8,000 p/w</td>
<td>£875K</td>
<td>12</td>
<td>11</td>
<td>10</td>
<td>2</td>
<td>12</td>
<td>12</td>
<td>12</td>
<td>11</td>
<td>13</td>
<td>8</td>
<td>10</td>
<td>5</td>
<td>2</td>
<td>1</td>
<td>3</td>
<td>9</td>
<td>7</td>
<td>6</td>
<td>3</td>
<td>3</td>
<td>2</td>
<td>7</td>
<td>1</td>
<td>2</td>
<td>5</td>
<td>7</td>
<td>12</td>
<td>14</td>
<td>13</td>
<td>13</td>
<td>11</td>
<td>17</td>
<td>1</td>
<td>12</td>
<td>7</td>
<td>12</td>
<td>15</td>
<td>10</td>
<td>15</td>
<td>10</td>
<td>13</td>
<td>12</td>
<td>12</td>
<td>12</td>
<td>8</td>
<td>15</td>
<td>14</td>
</tr>
</table>
<p>
<p align="center">
</body>
</html>
code i've created so far:
<?php
// Defining the basic scraping function
function scrape_between($data, $start, $end)
{
$data = stristr($data, $start); // Stripping all data from before $start
$data = substr($data, strlen($start)); // Stripping $start
$stop = stripos($data, $end); // Getting the position of the $end of the data to scrape
$data = substr($data, 0, $stop); // Stripping all data from after and including the $end of the data to scrape
return $data; // Returning the scraped data from the function
}
$myfile = fopen("source.html", "r") or die("Unable to open file!");
$page = fread($myfile,filesize("source.html"));
$page = scrape_between($page, '</tr>', '</table>');
$players = explode('<tr bgcolor="#EEEEEE">', $page);
echo '<pre>'; print_r($players); echo '</pre>';
fclose($myfile);
?>
Edited December 14, 2019 by seany1234 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 |
|||||||