|
PHP - Querying Mysql Columns Storing Comma Separeated Values
I created a simple search box which will query my table and match the input value to one of my columns. two of these columns store comma separated values. if i query a column other than a column which stores my csv i can see my search results. if i query a column which stores my csv i will not see results unless the search value matches the first value within the column. how would i be able to get say the second or third or forth value. here is the code i am using to query the table any help would be appreciated thanks.
Code: [Select] $q = $this->db->query("SELECT * FROM table WHERE col1 LIKE 'searchvalue'". " OR col2 LIKE 'searchvalue'". " OR col3 LIKE 'searchvalue'". " OR col4 LIKE 'searchvalue'". " OR FIND_IN_SET('searchvalue', col5) > 0 ". " OR FIND_IN_SET('searchvalue', col6) > 0"); Similar TutorialsHey there... I'm so stuck on this problem I was hoping someone could help... I need to query a MySQL Database using array values... Here is my array ... $query_keywords = Array ( [7] => business [10] => home [11] => depo ) Here is my php ...$query = "SELECT * FROM product WHERE product_name, product_comment IN ($query_keywords)"; $result=mysql_query($query); if(mysql_num_rows($result) > 0) { echo "results"; } else { echo "no results"; } Unfortunately, I get this ... Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in blah\blah\results.php on line 99 Please help me All comments greatly appreciated... Hello Guys, I am trying to insert a value in my table like this image.jpg,image2.jpg,image3.jpg Currently it just runs the image names together Here is my code.. I tried to use explode, but it just adds the word "array" in the table. Please advise $filename2 = $phstring; } $csv = $filename2; $fphoto = explode(',', $csv); // Insert ad details into mysql $query = "INSERT INTO ads (ad_type,ad_title,ad_body,ad_category,ad_photo,ad_member,ad_status,ad_city,ad_state,ad_zip)VALUES('".$ad_type."','".$ad_title."','".$ad_body."','".$catid."','".$fphoto."','".$vname."','".$ad_status."','".$city."','".$state."','".$zip."')"; This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=342695.0 Hello! I am designing a user registration form which has some radio buttons for people to select. I am confused as to how I should store the value selected by the user into the database. Here is my code, just in case. <li id="li_2" > <label class="description" for="element_2">I am a </label> <span> <input id="element_2_1" name="element_2" class="element radio" type="radio" value="1" /> <label class="choice" for="element_2_1">Student</label> <input id="element_2_2" name="element_2" class="element radio" type="radio" value="2" /> <label class="choice" for="element_2_2">Staff</label> <input id="element_2_3" name="element_2" class="element radio" type="radio" value="3" /> <label class="choice" for="element_2_3">Member</label> <input id="element_2_4" name="element_2" class="element radio" type="radio" value="4" /> <label class="choice" for="element_2_4">Other</label> </span> </li> helllo dear php-commmunity new to Ruby - i need some advices - i plan to do some requests in osm-files. (openstreetmap) Question - how can i store the results on a Database - eg mysql or - (if you prefer postgresql) - note: my favorite db - at least at the moment is mysql here the code require 'open-uri'
require "net/http"
require 'rexml/document'
def query_overpass(object_type, left,bottom,right,top, key, value)
base_url = "http://www.overpass-api.de/api/xapi?"
query_string = "#{object_type}[bbox=#{left},#{bottom},#{right},#{top}][#{key}=#{value}]"
url = "#{base_url}#{URI.encode(query_string)}"
resp = Net::HTTP.get_response(URI.parse(url))
data = resp.body
return data
end
overpass_result = REXML::Document.new(query_overpass("node", 7.1,51.2,7.2,51.3,"amenity","restaurant|pub|ice_cream|food_court|fast_food|cafe|biergarten|bar|bakery|steak|pasta|pizza|sushi|asia|nightclub"))
overpass_result.elements.each('osm/node') {|x|
if !x.elements["tag[@k='name']"].nil?
print x.elements["tag[@k='name']"].attributes["v"]
end
print " | "
if !x.elements["tag[@k='addr:postcode']"].nil?
print x.elements["tag[@k='addr:postcode']"].attributes["v"]
print ", "
end
if !x.elements["tag[@k='addr:city']"].nil?
print x.elements["tag[@k='addr:city']"].attributes["v"]
print ", "
end
if !x.elements["tag[@k='addr:street']"].nil?
print x.elements["tag[@k='addr:street']"].attributes["v"]
print ", "
end
if !x.elements["tag[@k='addr:housenumber']"].nil?
print x.elements["tag[@k='addr:housenumber']"].attributes["v"]
end
print " | "
print x.attributes["lat"]
print " | "
print x.attributes["lon"]
print " | "
if !x.elements["tag[@k='website']"].nil?
print x.elements["tag[@k='website']"].attributes["v"]
end
print " | "
if !x.elements["tag[@k='amenity']"].nil?
print x.elements["tag[@k='amenity']"].attributes["v"]
print " | "
end
puts
}
look forward to hear from you again - i would love to store it on a mysql - database - if possible. If you would prefer postgresql - then i would takte this one.... ;-) look forward to hear from you again - i would love to store it on a mysql - database - if possible. If you would prefer postgresql - then i would takte this one.... ;-) well - i guess that the answer to this will be the same no matter what language we are using. If the db is a sql database we need to design the database schema and create the tables in the database. The first step in accessing a db in our code is to get a connection to it. If ruby is our choice of language, a search for "ruby sql connector" will give us lots of options to read about. Well - we also can do it in PHP. What do you think!? Next, based on the schema we have designed, we need to create queries suitable for storing the data. We will likely need to consider our transactional model. Again, searching "ruby sql transactional model" will give us plenty of food for thought. Finally, we may want or need to close the connection to the database. i have a dictionary - THIS IS obviously a python dictionary an this has approx 8 000 lines with records [/CODE] $ python printer.py {'url': 'http://www.site1_com' 'cname': 'butcher', 'name': 'cheng', 'email': 'mail1@hotmail.com'} {'url': 'http://www.site2_com' 'cname': 'dilbert', 'name': 'James', 'email': 'mail2@hotmail.com'} [code=auto:0]i have a mysql-db up and runing in my opensuse there i have created a db with the fields url cname name i use the import MySQLdb i studied this documentation he http://stackoverflow...abase-in-python but i think this goes a bit over my head. - well how can i get the data ( in other words the dictionary) into the database? love to hear from you greetings This: Code: [Select] echo "<pre>"; print_r($torrent->content()); echo "</pre>"; Returns: Code: [Select] Array ( [Eureka.S05E01.HDTV.XviD-ASAP\Eureka.S05E01.HDTV.XviD-ASAP.avi] => 367011826 [Eureka.S05E01.HDTV.XviD-ASAP\eureka.s05e01.hdtv.xvid-asap.nfo] => 4798 [Eureka.S05E01.HDTV.XviD-ASAP\Sample\eureka.s05e01.hdtv.xvid-asap.sample.avi] => 7883092 ) Needs to be inserted to MySQL like: In other words, the array $torrent->content() needs to be inserted to MySQL as new lines at columns "filename" and "size". Please help me.. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313325.0 Hey guys, I'm new here so hello... but I'll get right to the point: I have a table named POSTS, where I store user posts. In this table, I have a row named "Popularity". Now I would like the value of "Popularity" to be a function of the number of Likes, Dislikes, Views and Date posted, all of which are stored in respective tables (linked by Primary and Secondary keys). I was wondering if it actually is possible to have the value of "Popularity" adjust automatically when a change in "Likes", "Dislikes" or "Views" occurs. And if not, what alternatives I have. The ultimate reason I need to have this value stored in a table and up to date is that I wish to sort posts by "Popularity". Many thanks !!! Hello all, I'm trying to get better at manipulating arrays and I'm stumped on this one. What would be the most efficient way of converting an array such as this: Quote Array ( [3] => 3 [9] => 1 [15] => 9 ) Into this: Quote 3,3,3,9,15,15,15,15,15,15,15,15,15 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=334481.0 I have a table in a mysql database with 5 columns, id, Name, Wifi, Bluetooth, GPS, with rows that are for example 1, Galaxy S2, yes, yes, yes. So basically i want to build a form that has check boxes (3 checkboxes for wifi bluetooth and GPS respectively) that once selected will query the database depending on which check boxes are selected. I have made the form but need to know what to put in the filter.php to make the results be displayed accordingly. Ideally if anyone knows how i would want it so the results were shown on the same page which i believe u need to use ajax to happen but any help on how to show the results will be greatful. the code for the form i have made is as follows: Code: [Select] <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>test</title> </head> <body> <form action="filter.php" method="post"> <INPUT TYPE=CHECKBOX NAME="option[]" VALUE="Wifi" id="r1">Wifi <INPUT TYPE=CHECKBOX NAME="option[]" VALUE="Bluetooth" id="b1">Bluetooth <INPUT TYPE=CHECKBOX NAME="option[]" VALUE="GPS" id="g1">GPS <input type="submit" name="formSubmit" value="Submit" /> </form> </body> </html> im not sure on how to make the filter.php page but i do have this code for displaying the all the data but i want to kno how to make it only display the data from the choices on the checkboxes Code: [Select] <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>pls work</title> </head> <body> <?php function h($s) { echo htmlspecialchars($s); } mysql_connect("localhost", "root", "") or die (mysql_error()); mysql_select_db("project") or die (mysql_error()); $result= mysql_query('SELECT * FROM test') or die('Error, query failed'); ?> <?php if (mysql_num_rows($result)==0) { ?> Database is empty <br/> <?php } else { ?> <table> <tr> <th></th> <th>Name</th> <th>Wifi</th> <th>Bluetooth</th> <th>GPS</th> </tr> <?php while ($row= mysql_fetch_assoc($result)) { ?> <tr> <td> <a href="uploaded-images/<?php h($row['Name']); ?>.jpg"> <img src="uploaded-images/<?php h($row['Name']); ?>.jpg" alt="test"/> </a> </td> <td><a href="textonly.html"><?php h($row['Name']); ?></a></td> <td><?php h($row['Wifi']); ?></td> <td><?php h($row['Bluetooth']); ?></td> <td><?php h($row['GPS']); ?></td> </tr> <?php } ?> </table> <?php } ?> </body> </html> I am trying to give role based access here. I want to display the pages which user has access in checkbox format. Here is my code
$sql = "SELECT p.page_id AS pid,
p.page,
p.href,
ra.pages AS rpage
FROM pages p
INNER JOIN role_access ra
WHERE p.page_id IN (ra.page)
AND ra.role=1";
$query = mysqli_query($con, $sql) or die(mysqli_error($con));
$checked_arr = array();
while($row = mysqli_fetch_array($query)) {
$checked_arr = explode(",",$row['rpage']);
foreach ($checked_arr as $page) {
echo "<br/><input type='checkbox' name=\"pages[]\" value='$page' />$page<br>";
}
My tables are like this ROLE CREATE TABLE `role` ( `rid` int(5) NOT NULL, `role_name` varchar(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `role` (`rid`, `role_name`) VALUES (1, 'Admin'), (2, 'Others'); ALTER TABLE `role` ADD PRIMARY KEY (`rid`); ROLE-ACCESS CREATE TABLE `role_access` ( `id` int(10) NOT NULL, `page` varchar(160) NOT NULL, `role` int(7) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `role_access` (`id`, `page`, `role`) VALUES (1, '1,2,3,4,5', 1), (2, '2,4,5', 2); ALTER TABLE `role_access` ADD PRIMARY KEY (`id`); PAGES CREATE TABLE `pages` ( `page_id` int(11) NOT NULL, `code` varchar(10) NOT NULL, `page` varchar(100) NOT NULL, `href` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `pages` (`page_id`, `code`, `page`, `href`) VALUES (1, 'Patient', 'Registration', '/patient_registration.php'), (2, 'Patient', 'List', '/patient_list.php'), (3, 'Patient', 'Edit', '/edit_patient.php'), (4, 'Patient', 'Export', '/export_patient.php'), (5, 'Patient', 'MRD', '/patient_MRD.php'); ALTER TABLE `pages` ADD PRIMARY KEY (`page_id`);
In above query i get result like this
But required result is
if i change checkbox display like
while($row = mysqli_fetch_array($query)) {
$checked_arr = explode(",",$row['rpage']);
$pname = $row['page'];
foreach ($checked_arr as $page) {
echo "<br/><input type='checkbox' name=\"pages[]\" value='$page' />$pname<br>";
}
}
i get result
How to get the names for that file.
I have a file that is uploaded by the user that has a series of rows of data in an html table. The columns are always a constant, but the number of rows in the file can change. These rows of the html table are the only information in the file when I am processing it. I need to either convert the file to a CSV file or write the values to a CSV list so that I can then insert them into a database. Any suggestions would be wonderful...I've spent the last three hours spinning my wheels. Here is an example of one row of data from the file: Code: [Select] <tr><td bgcolor=#dddddd class=dataFT >5 (Excellent)</td><td bgcolor=#dddddd class=dataFT >5 (Excellent)</td><td bgcolor=#dddddd class=dataFT >5 (Excellent)</td><td bgcolor=#dddddd class=dataFT > </td><td bgcolor=#dddddd class=dataFT > </td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >1015020</td><td bgcolor=#dddddd class=dataFT >155498322</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >4731751</td><td bgcolor=#dddddd class=dataFT >2011-09-25 11:18:33 (-5:00)</td><td bgcolor=#dddddd class=dataFT >2011-09-25 12:16:50 (-5:00)</td><td bgcolor=#dddddd class=dataFT >0:58:17</td><td bgcolor=#dddddd class=dataFT >0:58:16</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >Resolved</td></tr> Hey guys, What would you recommend to store db values for later? the only thing I can come up with is doing it this way: $result = mysql_query("SELECT * FROM users WHERE name='$user'"); while($row = mysql_fetch_array($result)) { } { and then using the $row['dbvariable'] later is that the best way? Hi all, I need to create a table which holds a list of values, for EG 1 PHP 2 MySQL 3 HTML 4 JS The reason im doing this is so when a user visits X page on my site, they are presented with a list of possible options which they can tick, for instance if you were asked what languages your fluent with you would tick HTML and JS (or w/e applies to you) What would be the best way of constructing the table? Thanks Hi guys, Im currently doing a project for college and i have experienced some problems with saving my php drop down values to my database. im am getting no error but my values are not being stored to my db.If anyone could help me or point me in the correct direction then id be grateful. Ive attached my code. This topic has been moved to Application Design. http://www.phpfreaks.com/forums/index.php?topic=311333.0 i want to store some data that i have gained from a request at the planetfile of openstreetmap: so i have set up a database on my opensuse 13.1 i have created a db called test on the mysql-server on my opensuse: 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`) ) Where each tagname/value pair is stored as a row in a separate table with the pois id Processing would be like this <?php
$db = new mysqli(localhost,root,rimbaud,'test'); // use your credentials
$xmlstr = <<<XML
<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>
</data>
XML;
$fields = array('id','name','lat','lon');
$xml = simplexml_load_string($xmlstr);
//
// PROCESS XML RECORDS
//
$poisdata = array();
$tagdata = array();
foreach ($xml->node as $node) {
$nodedata = array_fill_keys($fields,'');
$nodedata['id'] = intval($node['id']);
$nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0;
$nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0;
$poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata);
foreach ($node->tag as $tag) {
$k = (string)$tag['k'];
$v = (string)$tag['v'];
$tagdata[] = sprintf("(%d, '%s', '%s')"
, $nodedata['id']
, $db->real_escape_string($k)
, $db->real_escape_string($v));
}
}
//
// STORE THE DATA
//
$sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata);
$db->query($sql);
$sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata);
$db->query($sql);
//
// DISPLAY THE DATA
//
$currentTags = array();
$sql = "SELECT DISTINCT tagname
FROM pois_tag
ORDER BY tagname = 'name' DESC, tagname";
$res = $db->query($sql);
while (list($tn) = $res->fetch_row()) {
$currentTags[] = $tn;
}
$thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>"
. join('</th><th>', $currentTags) . "</th></tr>\n";
$currid = $currlat = $currlon = 0;
$sql = "SELECT p.id, lat, lon, tagname, tagvalue
FROM pois p
LEFT JOIN pois_tag t ON t.poisid = p.id
ORDER BY p.id";
$res = $db->query($sql);
$tdata = '';
while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) {
if ($currid != $id) {
if ($currid) {
$tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>"
. join('</td><td>', $poisrow) . "</td></tr>\n";
}
$currid = $id;
$currlat = $lat;
$currlon = $lon;
$poisrow = array_fill_keys($currentTags,'');
}
$poisrow[$t] = $v;
}
$tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>"
. join('</td><td>', $poisrow) . "</td></tr>\n";
?>
<html>
<head>
<meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tags</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="06/04/2014">
<style type="text/css">
body, td, th {
font-family: arial, sans-serif;
font-size: 10pt;
}
table {
border-collapse: collapse;
}
th {
background-color: #369;
color: white;
padding: 5px 2px;
}
td {
background-color: #EEE;
padding: 2px;
}
</style>
</head>
<body>
<table border='1'>
<?php echo $thead, $tdata; ?>
</table>
</body>
</html>
leeeds to the following errors.... PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121 martin@linux-70ce:~/php> php osm_200.php PHP Notice: Use of undefined constant localhost - assumed 'localhost' in /home/martin/php/osm_200.php on line 3 PHP Notice: Use of undefined constant root - assumed 'root' in /home/martin/php/osm_200.php on line 3 PHP Notice: Use of undefined constant rimbaud - assumed 'rimbaud' in /home/martin/php/osm_200.php on line 3 PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121 martin@linux-70ce:~/php> so what goes wrong here...? This is more of a database architect question.
I am building a CMS for website development. I am ready to use it for the first time on one of my clients. Now, my issue is is how to set up some or one of my table(s) for some areas that my client wants to be able to change in the admin section.
In their website on the front page, I put together this nice block slider. Its 6 blocks with text, a background image and nice effects. At first it was always going to be static, which was no issue at all, but later they came to me and asked if they could be able to change words in the boxes themselves in the admin section..
Of course this can be easily done by creating a table that is associated with this home page block thing and each row in the table will correspond to one of the blocks on the home page.
But I believe there might be an easier way to store this type of data without having to create another table for every feature the client wants to edit, for example they also want to be able to edit a slider on another page.
The WordPress database then came into my mind and how they store most of their data as JSON. So I was then thinking of creating a table named 'modules' for examples with three columns (id, title, data). Then inside the data column store the editable fields as a JSON string object.
For example a row could like like :
1, "Front Page Box Slider", {"slider-home" : [ {"data" : "value", "img_1", "value1"}, {"data" : "value", "img_2", "value2"} ]}
I've been doing a bit of research on this and people have just been saying, never do this if you do want your data to be searched through, which I don't.
Can anyone tell me why or why not I should do this, or maybe a better solution to my problem?
Thank you
Edited by carlosmoreeira, 17 September 2014 - 07:37 PM. |
|||||||