|
PHP - To Work With Db-fields: Hardcoded Or Not - In Order To Be Flexible
Hello dear php-experts - good day.
note; i run linux opensuse 13.1 the final goal is to get stored some xml-files in a mysql-database. thats what i am looking for. - the xml-files are derived from a osm-request - at a OpenSteetpmap-api. and thats why i am here. Guess that you are good php and mysql-experts well i play around with some openstreetmap-requests - and want to store the results in a Mysql-db. see the tags: id lat lon name amenity operator vendingsee the table 'pois' that i want to create - and subsequently create some columns see the SQL-Script CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci; USE hans; CREATE TABLE `pois` ( `id` BIGINT(20) UNSIGNED NOT NULL, `lat` FLOAT(10,7) NOT NULL, `lon` FLOAT(10,7) NOT NULL, `name` VARCHAR(255) COLLATE utf8_bin NOT NULL, `amenity` VARCHAR(255) COLLATE utf8_bin NOT NULL, `operator` VARCHAR(255) COLLATE utf8_bin NOT NULL, `vending` VARCHAR(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; first of all i thought that i can do the db creation with some hard coded methods: but instead of hard-coding the required column names as in $fields = array('id','lat','lon','name','amenity','operator','vending'); we could use this to pick up any additional columns added to your pois table
$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
$fields[] = $row[0];
}
btw: see the data <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> with the following fields in the db: CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci; USE hans; CREATE TABLE `pois` ( `id` BIGINT(20) UNSIGNED NOT NULL, `lat` FLOAT(10,7) NOT NULL, `lon` FLOAT(10,7) NOT NULL, `name` VARCHAR(255) COLLATE utf8_bin NOT NULL, `amenity` VARCHAR(255) COLLATE utf8_bin NOT NULL, `operator` VARCHAR(255) COLLATE utf8_bin NOT NULL, `vending` VARCHAR(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; EDITED BY MODERATOR - DB CREDENTIALS REMOVED see the dataset; - which is stored in the file mysql.txt the dataset - it is gathered from the request on the overpass-api which resides here http://www.overpass-turbo.eu cf. http://overpass-turb...Pg&c=BNJBWRBb1P you see a request on the left part of the screen note: to get the output - just press the button in the top-menu called "Ausführen" after this you press the button called "DATEN" on the top-right - just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen. note - it has got various ids - that means that the osm-file does not give back constantly all the tags... the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!? see the output he <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> well you see that i have some questions the second one is regarding the variations in the mysql.txt - file - i.e. the different number of tags.; How to make the script robust so that it is able to work with this - and does not stopt to work....!? well i hope i was able to make clear what i want to achieve: can i use the code snippet to create the db table - we could use this to pick up any additional columns added to your pois table
$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
$fields[] = $row[0];
}
note: i need to have a robust way - where i can cope with xmlfiles that are
full with additionals tags /(that make it necessary to have more collumns added.
I look forward to hear from youmany many greetings Similar TutorialsThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=320026.0 Any idea why I cant get the order by to work? $res = mysql_query ("SELECT COUNT(*), LEFT(`comment`, 3) AS truncated from comments where navn='$rows[navn]' AND adresse='$rows[adresse]' ORDER BY time desc" ) or die(mysql_error()); This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=354151.0 Hello dear friends, hello community, i want to parse a site http://www.aktive-buergerschaft.de/buergerstiftungsfinder Well therefore i have a true beginner-question regarding Array in order to work with the data: beginner question The results should be stored in a MySQL-Database: well you see. i have a Curl-Approach: and i want to work with the parsed data: and in this case i want to give over the data into an array (if viewing the output in web browser, do view-source to see structured array). Here the musings that led to the code: Depending on num dimensions, we could do, foreach($result as $k => $v) {echo $k." ".$v} to view and work with data (adding sub foreach loops accordingly; i.e. if $v is itself an array) see the results: What do you think: Code: [Select] $ch = curl_init("http://www.aktive-buergerschaft.de/buergerstiftungsfinder"); curl_setopt($ch, CURLOPT_HEADER, 0); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); $buffer = curl_exec($ch); curl_close($ch); $result = explode(",", $buffer); foreach($result as $k => $v) {echo $k." ".$v} if $v is itself an array) // print_r($result); look forward to hear from you!! db1 Hi folks, this is my first post to phpfreaks so I wanted to start with a discussion regarding flexible and testable PHP designs, something I'm struggling with. I'm trying to design a couple of components for a PHP project that I'm working on. It relies on the Zend Framework and is covered by PHPUnit tests. I have two sub-systems: video storage manager and user account manager. I need help with the design of these managers. They need to be developed in a way that allows me to swap out the backend (what actually does the video storing / user handling) with minimal changes to systems that depend on these managers. Since I'm using the Zend Framework these managers will be used in various controllers. I'm using PHPUnit to test the controllers (as outlined in the Zend Framework's Documentation- Zend_Test_PHPUnit) and it is possible that the managers' backend will be provided by 3rd party classes (or wrappers around 3rd party classes). So the design must allow the backend to be easily mocked. I'm interested in hearing some of the various approaches for designing the video storage and user account managers and their backend classes / wrappers. - Nick I have a menu-item that links to for example example.com?page=y Then I need my php code to pull that "y" and put it in "include("pages/'$thatpage'.php");" Got this, that doesn't work: if (isset($_GET['page']) && $_GET['page'] == $page) { include("pages/'$page'.php"); } else { include("pages/index.php"); } ^ $page should then become the desired page, for example "y" This does though: if (isset($_GET['page']) && $_GET['page'] == y) { include("pages/y.php"); } else { include("pages/index.php"); } But i need it to be flexible, so i don't have to update my file every time i add a new page. Lets say that I have an array that I want to convert to a value object My value object class is as follows: /* file UserVO.php*/ class UserVO { public $id; public $email; public function __construct($data) { $this->id = (int)$data['id']; $this->email = $data['email']; } } And I create my array of value objects as follows: /* file UserService.php*/ $array = array( array(...), array(...)); $count = count($array); for ($i = 0; $i < $count; $i++) { $result[] = new UserVO($array[$i]); } return $result; OK, so this all works fine. However, I'd like to specificy the VO that is to be created dynamically, so that I can have a single dynamic function to create my VO's. Something like: $ret = create_vo($array, 'UserVO'); function create_vo($data, $vo) { $count = count($data); for ($i = 0; $i < $count; $i++) { $result[] = new $vo($array[$i]); //this obviously wont work...Class name must be a valid object or a string } return $result; } I realise that I could do this with a switch statement (iterating through all my VO's)...but there is no doubt a much much more elegant solution. It would also be supercool if I could lazy load the VO's as needed, instead of having multiple 'includes' Any help much appreciated. Hey all, If I want to have a category which can have subcategories, which in turn can have its own subcategories or belong to the parent category, and the subcategories can have more than one parent categories, is the most effective way to design this is to create a categories table and categories_join table where the categories table has a foreign key categories_join_id linking it to the categories_join table and the categories_join table has a field called parent_id which associates with the primary key of the categories table, therefore allowing me to have multiple subcategories to one category: categories_join table id parent_id 1 2 1 3 categories table id categories_join_id 1 1 2 2 3 3 So from the example above, the first category has two parents, category 2 and category 3. As an alternative option, in this post: http://stackoverflow.com/questions/5384183/database-design-question-categories-subcategories The second answer down mentions to use recursive programming? But he doesn't give an example of what he means. Is he saying you have a function call itself passing it parameters as to what the parent should be: function getParent($category,$parents = array()){ } Thanks for response. Hi there - can anyone lend me a little advise as to what I'm doing wrong here. I have this line: $query=mysql_query("SELECT * FROM gallery a JOIN gallery_image b ON a.id = b.gallery_id where gallery_id=$gallery_id"); I am trying to add an order by statement: order by gallery_image.priority So I created: $query=mysql_query("SELECT * FROM gallery a JOIN gallery_image b ON a.id = b.gallery_id where gallery_id=$gallery_id order by gallery_image.priority"); But, I receive the error (even though there is table called "Gallery Image" and a column called "Priority": Code: [Select] Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/***/public_html/***/gallery.php on line 5 select: Unknown column 'gallery_image.priority' in 'order clause'
Hi everyone. I'm very new into self learning programming. Presently I'm trying to develop a simple basic Robot that would only Place a Market Order every seconds and it will cancel the Order every followed seconds. Using the following library: It would place Trade Order at a ( Price = ex.com api * Binance api Aggregate Trades Price) I have already wrote the api to call for xe.com exchange rate with php
<?php
$auth = base64_encode("username:password");
$context = stream_context_create([
"http" => [
"header" => "Authorization: Basic $auth"
]
]);
$homepage = file_get_contents("https://xecdapi.xe.com/v1/convert_from?to=NGN&amount=1.195", false, $context );
$json = json_decode($homepage, TRUE);
foreach ($json as $k=>$to){
echo $k; // etc
};
?>
And also for the Binance Aggregate Price in JavaScript
<script>
var burl = "https://api3.binance.com";
var query = '/api/v3/aggTrades';
query += '?symbol=BTCUSDT';
var url = burl + query;
var ourRequest = new XMLHttpRequest();
ourRequest.open('GET',url,true);
ourRequest.onload = function(){
console.log(ourRequest.responseText);
}
ourRequest.send();
</script>
My problem is how to handle these two api responds and also the functions to use them to place a trade and cancel it. Hey guys,
Thank you in advance... here is my situation, I have a form with three (3) fields in it, the 'student name' is unlimited textfield with an "add more" button to it and I have two select fields ('number of shirts' and 'trophies') that depend on the number of entries for 'student name'...
I want to create the select fields based on this math, for as many 'student name' entries:
1- i want to have the select form for 'number of shirts' to be 0 up to that number... so if there are 6 'student name' entries, the select options will be 0,1,3,4,5,6,7
2- I want to have the select form for 'trophies' to be 5 'student name' entries to 1 'trophies', for example if there are 6 'student name' entries, the select options will be 0,1... if there are 13 entries, options will be 0,1,2... So if there are less than 5 'student name' entries, the select field will not show (hidden)
of course if there are no 'student name' entries, these two fields won't show up (hidden)
let me know if that make sense and ANY help or directions will be GREATLY APPRECIATED.
Thanks guys!
I've got. This one requires lots of up front information: I have a page, for this example that I will call page.php. It takes get parameters, and for this example I'll call the parameter "step". So I have a URL like this: page.php?step=1 This page has a form with an action of page.php?step=1. The code on the page validates the posting information. If the information is bad, it returns the user to page.php?step=1; if it is good, it takes the user to page.php?step=2 via header( "location:page.php?step=2" ). So redirection is done by relative path, not full URLs. This all works as expected. Now what I've done is set .htaccess to be HTTPS for this page, via this code: # Turn SSL on for payments RewriteCond %{HTTPS} off RewriteCond %{SCRIPT_FILENAME} \/page\.php [NC] RewriteRule ^(.*)$ https://%{HTTP_HOST}/$1 [R=301,L] This works (initially). However, once you try to post the form, it just redirects back to the step=1 version of the page. I really don't know how or why that would be. I'm not sure how else I can explain this or what other information you may have. But it's frustrating to not get a page working in HTTPS that works in HTTP. Very odd. Any suggestions? (I don't even really know the best location to figure out when/why it's redirecting back to the original page.) Hi.., I use below method to export data to excel. header('Content-type: application/ms-excel'); header('Content-Disposition: attachment; filename=abc.xls'); if I run the script from the server. (http://localhost/export.php) it is work. (pop-up window if i want save or open the file) but if i run the script from the client (http://192.168.1.5/export.php) it is not work. (nothing happen) any idea how to solve this? require_once 'includes/upload.class.php'; $upload = new uploads(); $details = $upload->getFileInformation($id); <?php echo $details['upload_desc']; ?> then here the class. require_once 'db.class.php'; class uploads extends database { private $uploadData; function uploadFile() { public function getFileInformation($id) { $this->uploadData = $this->readData("uploadfiles", "upload_id", $id); return $this->uploadData; } But it wont work! Some of you may have seen one of my many posts about email issues. Some users don't get them, and I have determined it is probably because we are marked as spam.
We are a service that grades sales team members on their phone skills. Listening to pre-recorded calls, grading and uploading them to our site, and then another part of our business looks them over and sometimes leaves a message that then get's forwarded to this persons work email.
I have determined there is ways to get marked as spam as default by not having an opt out link. This is not an option, these sales members employer has opted in, and the emails are going to work related accounts hosted at that employer. Also, if one of these staff members is not so bright, or disgruntled they may mark us as spam anyways. The bottom line is that we have very little control over whether we are or are not marked as spam.
So we want to start looking into sending text messages and this is where I start to question how good of an idea this is.
First off, if it was me, and the messages where being sent to a device that my employer did not provide, I would in no way want work related text messages coming to me. Unless there is a vested interest in getting them. IE, I'm the boss at this place and am always on the clock. What if you are on the bottom? It's just a job for you.
What if it is a pre-paid device, text messages cost money. What then? What if they don't even have, or want a cell phone?
The short of it is this. If I'm at a job that is just another job, and this employer tells me that I have to get these messages. I'm going to look for another job. I see the organizations having continuous issues and complaints from their employees. Thus us as a business having issues keeping clients.
What am I getting into here? What are your opinions on this matter? What are your recommendations as to alerting users of something on our site that we can rest assured are being received 100% of the time?
Thanks!
Nick
I need to make 0, which displays as POA appear at the ned of the list not at the begining, I currently just use order by price asc and 0 is at the beginning, i need to make 0 at the end Hello, I have classes in a database with no set UNIX date, just the day like Wednesday and in two other columns the start and end dates. I want to be able to order by the day first and then by end_time but php orders the day column by spelling and not the day it holds in chronological order. Is there anyway to change the query to order the day column as a date? See the query below? Code: [Select] $query = "SELECT * FROM zumba_timetable WHERE end_time>'$current_time' ORDER BY day, end_time ASC LIMIT 0,1"; Hi there, just registered and in need of help, this looks a good place to start! I'm a php beginner so please bear with me I have a mysql database which holds 3 pieces of info id: match: 1: I'm trying to sort the results using ORDER BY match ASC but for some reason it's just not for having it... Can't work out if it's an error with my php code or my table. I can order by "ID" no problem but I when I try ordering alphabetically by "match" it won't. my code is: Code: [Select] mysql_connect("***.***.***") or die(mysql_error()); mysql_select_db("database") or die(mysql_error()); $query = "SELECT * FROM employees WHERE flag = 'tv' ORDER BY match ASC"; $result = mysql_query($query) or die ("Query failed"); $numrows = (mysql_num_rows ($result)); // loop to create rows if($numrows >0){ echo "<table width = 100% border = '0' cellspacing = '2' cellpadding = '0' >"; // loop to create columns $position = 1; while ($row = mysql_fetch_array($result)){ if($position == 1){echo "<tr>";} echo " <td align = 'center'><a href=\"http://www.website.eu/sport-stream/1/{$row['id']}.html\" target=_blank>{$row['match']} <br> <img src=\"{$row['8']}\" width=\"100\" height=\"70\" /> </a> </td> "; if($position == 4){echo "</tr> "; $position = 1;}else{ $position++;} }//while $end = ""; if($position != 1){ for($z=(4-$position); $z>0 ; $z--){ $end .= "<td></td>"; } $end .= "</tr>"; } echo $end."</table> "; }//if And here's a cap of my database: any help for a php n00b would be greatly appreciated! I got this code from a previous thread: Code: [Select] mysql_query("SET @rows = 0;"); $res = mysql_query("SELECT @rows:=@rows+1 AS view_rank,COUNT(id) AS views, credit_members_id FROM vtp_tracking GROUP BY credit_members_id ORDER BY views DESC"); $n = array(1 => 'st', 2 => 'nd', 3 => 'rd'); while($row = mysql_fetch_row($res)) { if ( $row[2] != $members_id ) continue; if ( substr($row[0], -1) < 4 ) { $row[0] .= $n[$row[0]]; } else { $row[0] .= 'th'; } echo ' You are in ' . $row[0] . ' place with ' . number_format($row[1]) . ' views.'; break; } Everything seems ok except it orders by the "credit_members_id" and not "views" as entered. Can someone explain why, and how to fix this? |
|||||||