|
PHP - How To Poll Mysql For Results Of All 50 States?
I'm needing some direction in how to start this... I have a page with the 50 U.S. states listed. I have a column in my mysql database that holds each state's name called: source
There's a few thousands rows in my database, so the state names vary in the record count. How can I query the database and have a little count by each one of my 50 states that shows how many results exist in the db for that state? I know how to query for total rows and show that number... but confused on how I would do it for individual line items. Thanks for any suggestions. Similar TutorialsI've got 2 database tables (states, stores) and I want to output all the states with a list of specific locations under each state. My 'states' table has a 'state_id' and 'state' while my 'stores' table has all the store information along with the 'state_id' to work with the 'states' table. How would I ago about formulating a loop to display all locations organized by each state, etc.? Hello, I use loads of different servers at work and they all run seperate services that I need to keep my stuff running. I want to build a simple webpage that will poll the services and display the current state by colour. EG have table that lists the services in green and have them turn red if the service stops unexpectedly. I can build the tables and stuff, I just don't know how to retrieve the state of a service. I've been messing about with some ideas but nothing is working. eg lets say I have two servers each with one service: service one | IP Address of server | RUNNING service two | IP Address of server | STOPPED Has anyone got any ideas? Thanks!! I am having a helluva time trying to populate my state option box with a state array. I have the array set but now how does this get called into the foreach block where the select box is? I can populate it i guess with options manually but I want to get the array to display in the box instead. I tried everything with the minimal experience I have so I need a little help, I got the select box up and displaying where I want it but I cant populate it with an array call. See foreach block near the end of the code: Thanks a bunch! here is my include file code so far: <?php /* Program name: form_test_state.inc * Description: Defines a form that collects a user's * name and mailing address. */ $rows = array( "first_name" => "First Name", "last_name" => "Last Name", "phone" => "Phone Number", "city" => "City", "state" => "State", "address" => "Address", "zip_code" => "ZIP Code", "e_mail" => "E-Mail"); $states_list = array('AL'=>"Alabama",'AK'=>"Alaska",'AZ'=>"Arizona",'AR'=>"Arkansas",'CA'=>"California",'CO'=>"Colorado",'CT'=>"Connecticut",'DE'=>"Delaware",'DC'=>"District Of Columbia",'FL'=>"Florida",'GA'=>"Georgia",'HI'=>"Hawaii",'ID'=>"Idaho",'IL'=>"Illinois", 'IN'=>"Indiana", 'IA'=>"Iowa", 'KS'=>"Kansas",'KY'=>"Kentucky",'LA'=>"Louisiana",'ME'=>"Maine",'MD'=>"Maryland", 'MA'=>"Massachusetts",'MI'=>"Michigan",'MN'=>"Minnesota",'MS'=>"Mississippi",'MO'=>"Missouri",'MT'=>"Montana",'NE'=>"Nebraska",'NV'=>"Nevada",'NH'=>"New Hampshire",'NJ'=>"New Jersey",'NM'=>"New Mexico",'NY'=>"New York",'NC'=>"North Carolina",'ND'=>"North Dakota",'OH'=>"Ohio",'OK'=>"Oklahoma", 'OR'=>"Oregon",'PA'=>"Pennsylvania",'RI'=>"Rhode Island",'SC'=>"South Carolina",'SD'=>"South Dakota",'TN'=>"Tennessee",'TX'=>"Texas",'UT'=>"Utah",'VT'=>"Vermont",'VA'=>"Virginia",'WA'=>"Washington",'WV'=>"West Virginia",'WI'=>"Wisconsin",'WY'=>"Wyoming"); $submit = "Submit mailing information"; ?> <html> <head> <style type='text/css'> <!-- body { background: #42413C; color: #000; font-family: Tahoma, Geneva, sans-serif; font-size: 90%; } form { margin: 1em 2 2 2; padding: 1; width: 1100px; margin-left: 35%; } .field {padding-bottom: 1em; } label { font-weight: bold; float: left; width: 10%; margin-right: 1em; text-align: right; background-color: #9F6; padding-right: 5px; } select {margin-bottom: 1em;} #submit { margin-left: 15%; } h3 { width: 500px; margin-left: 35%; } --> </style> </head> <body> <h3>Please enter your mailing information below:</h3> <?php /* loop that displays the form */ echo "<form action='checkBlankOnly_latest.php' method='post'>"; foreach($rows as $field => $label) if($field == "state") { echo "<label for='$field'>$label</label> <select name='State'> <<<< Insert $states_list array here I am trying to figure out >>>> <option value='state'>Alabama </option> </select> "; } else { echo "<div class='field'><label for='$field'>$label</label> <input id='$field' name='$field' type='text' value='".@$$field."' size='25%' maxlength='65' /></div>\n"; } echo "<div id='submit'> <input type='submit' value='$submit'></div>"; ?> </form> </body> </html> How can I limit the amount of time my while loop to only show the first 4 rows of my SQL table?
I am using this code to loop my whole table:
while($row = $results->fetch(PDO::FETCH_ASSOC))
{
echo '
<li>Mileage: '.number_format($row["Mileage"]).'</li>
<li>Engine size: '.$row["EngineSize"].'cc</li>
';} ?>
I only want to loop through the first 4 rows of my SQL table, I then want to duplicate the same code but start at the 5th row until the 8th row of the table, how can I do this?
Thanks, NickWhat's the best way to format results from a MySQL table? I've found very vague examples of PHP code utilizing html tables. Below is my current PHP. Thanks! <?php $con = mysql_connect("localhost","xxxxx","xxxxx") or die('Could not connect: ' . mysql_error()); mysql_select_db("addresses", $con); $result = mysql_query("SELECT * FROM addresses"); while($row = mysql_fetch_array($result)) { echo $row['first_name'] . " " . $row['last_name'] . " " . $row['extra_info'] . " " . $row['address'] . " " . $row['city'] . " " . $row['state'] . " " . $row['zip']; echo "<br />"; } mysql_close($con); ?> I'm working on a website that uses a database to display the employees from different states. Below is the code I'm using and everything works great, but I can't figure out why all the results are getting posted twice. I'm trying to isolate just one state so we can have a different contact page for each state. Thanks for any help! Please let me know if you need more information. (I'm attaching a screenshot of what's happening as well.) Code: [Select] <? include "public/public_common.php"; //Open Database Connection $db = open_db_connection(); $sql = "SELECT DISTINCT state_full = 'california' from users where active = 'Y' and show_on_contactus = 'Y'"; $results = mysql_query($sql, $db); if(!mysql_num_rows($results) == FALSE) { while($row = mysql_fetch_array($results, MYSQL_BOTH)) { echo "<ul id=\"directors\">\n"; $state_sql = "SELECT * from users where active = 'Y' and show_on_contactus = 'Y' and state_full = 'california' order by last_name"; $state_results = mysql_query($state_sql, $db); if(!mysql_num_rows($results) == FALSE) { while($state_row = mysql_fetch_array($state_results, MYSQL_BOTH)) { echo "<li>\n"; echo " <div class=\"fl dirimg\">\n"; if($state_row[photo] == "") { echo "<img border=\"0\" width=\"61\" height=\"85\" src=\"images/exe_placeholder.jpg\" alt=\"$state_row[first_name] $state_row[last_name]\" />\n"; } else { echo "<img border=\"0\" width=\"61\" height=\"85\" src=\"n2team/pictures/$state_row[photo]\" alt=\"$state_row[firstname] $state_row[last_name]\" />\n"; } echo " </div>\n"; echo " <span class=\"fr\">$state_row[first_name] $state_row[last_name]<br />\n"; echo " $state_row[title]<br />\n"; echo " $state_row[city], $state_row[state]<br />\n"; //echo " <a href=\"mailto:$state_row[email]\">$state_row[email]</a></span></li>\n"; } } echo "</ul>\n"; } } ?> Hey everyone. So I am coding a badge system for my website usersystem. I am trying to think how I'll handle issues before actually starting to code it and one issue I just can't seem to figure out how to handle.. How would I limit the amount of MySQL results to show per line. Eg. A user has 6 badges but I'd only like to show 2 per line. So in total it'd show 3 lines with 2 badges per line. If that makes any sense? Is there any way possible to actually do this? I have a problem displaying the results of a table, It shows them in one long list and I'd like them to show as 2 or 3. I had a look around and the most favoured way to this I found was to split the results either by odd/even or using percentages but Im having trouble implementing it into my script. How would I go about adding the odd/even way to this: <?php $query = mysql_query('SELECT * FROM users ORDER BY Username'); while ($row = mysql_fetch_array($query)) { if ($row['UserID']) ?> <b>Username:</b> <span class=class1><a href="aeditprofile.php?username=<? echo $row['Username'] ?>"><? echo $row['Username'] ?></a></span><br> <b>Group:</b> <? echo $row['Level'] ?></color><br /><hr> <?php } ?> Essentially I'm after getting something like this: Name Name Name Group Group Group and so on down the page.... Hello, This is query in MySQL Quote mysql> LOAD DATA LOCAL INFILE '/var/www/html/numbers.csv' INTO TABLE details FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (number); Query OK, 0 rows affected (0.00 sec) Records: 2200 Deleted: 0 Skipped: 1200 Warnings: 0 Now, I am trying to run the same query in PHP and display the same results.... (i.e. Records, Deleted, Skipped) Quote <?php $db=mysql_connect("localhost", "user", "1234") or die(mysql_error()); $dname="database"; mysql_select_db($dname)or die(mysql_error()); $sqlstatement="LOAD DATA LOCAL INFILE '/var/www/html/numbers.csv' INTO TABLE details FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (number)" ; mysql_query($sqlstatement) or die(mysql_error());; echo "it is done!"; ?> Tried mysql_fetch_array, row but could not get it to work... please help guys this is my code but i want to have a pice so if their is no results the it will put in a message like "No Results" but i cant figger it out <?php $host="localhost"; $username="xxxxx"; $password="xxxxxx"; $db_name="xxxxxxx"; $tbl_name="Shows"; // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT * FROM $tbl_name WHERE Month='April'and Year='2011' order by Day asc "; $result=mysql_query($sql); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td> <table width="400" border="1" cellspacing="0" cellpadding="3"> <?php while($rows=mysql_fetch_array($result)){ ?> <span class="elevenbold"><? echo $rows['Day']; ?> <? echo $rows['Month']; ?> <br><strong class="elevenboldblue"><? echo $rows['Song']; ?></strong><br><? echo $rows['Data']; ?></span><br><br> <?php } ?> </table> </td> </tr> </table> <?php mysql_close(); ?> Hey Everybody, I am writing a SUPER SIMPLE script and for some reason I cannot figure this issue out. I guess I'm too close to the situation and have spent too many hours staring at this script. Here's the problem: I am running a basic SQL query through php that should return multiple rows of data and instead returns the first row multiple times. I'm not sure what the problem is, but I'm sure YOU can help! <?php //Get Invoice Rows $sql = 'SELECT * FROM timecard WHERE INVOICE_ID=\'1000\''; $result = mysql_query($sql); $rows = mysql_fetch_array($result); $num = mysql_num_rows($result); //Build Current Invoice $i=0; $invoice = '<table class="invoice" cellspacing="0" cellpadding="0">'; $invoice .= '<tr class="heading"><td>#</td><td>Invoice</td><td>Date</td><td>Time In</td><td>Time Out</td><td>Hours</td><td>$/Hr</td><td>Sub Total</td></tr>'; while($i < $num){ if( $i%2 ) { $eo = 'odd'; } else { $eo = 'even'; } $invoice .= '<tr id="invoiceRow" class="'.$eo.'"><td>'.$rows[0].'</td><td>PHG'.$rows[1].'</td><td>'.$rows[2].', '.$rows[4].' '.$rows[3].', '.$rows[5].'</td><td>'.$rows[6].'</td><td>'.$rows[7].'</td><td>'.$rows[8].'</td><td>'.$rows[9].'</td><td>'.$rows[10].'</td></tr>'; $runningTotal[$i] = $rows[10]; $i++; } //Get Total $total = array_sum($runningTotal); $invoice .= '<tr><td colspan="7" style="background-color: #000000; color: #ffffff; font-weight: bold; padding-left: 5px;">Total</td><td align="right" style="background-color: #333333; font-weight: bold; color: #FFFFFF; padding-right: 5px;">'.$total.'</td>'; $invoice .= '</table>'; echo $invoice; ?> Much thanks in advance for anyone that is able to resolve this problem, even just a try is nice!! Thank You, E I'm having two issues:
(1) The correct mysql query for multiple rows
(2) The if/elseif/else to pull data from these rows, and process it based on the row data
I'm only pulling data where A=1 and B=1,2 -- so two possible entries there (call them B1 and B2). I need a php if statement to choose whether the output of B is one of two urls (b1=google,b2=bing). The actual script is far more complex, with more than just 1,2 from B. I've stripped all the excesses down to this one if/else issue and the db query. The output php doesn't matter here. And I can add more elseif once this problem is solved.
<?php
mysql_connect(localhost, $db_username, $db_password);
@mysql_select_db($db_database) or die("No connection");
$query = "Select * FROM table WHERE column='stuff' AND parent='1,2' ORDER BY id DESC LIMIT 10";
$query_result = mysql_query($query);
$num_rows = mysql_num_rows($query_result);
mysql_close();
?>
<?php
for($i=0; $i< $num_rows; $i++){ //start a loop
$stuff = mysql_result($query_result, $i, "column");
$row = mysql_fetch_assoc($query_result, $i);
if($row['parent'] == 1) {
$url = 'http://google.com';
} else {
$url = 'http://www.bing.com';
}
?>
My own first attempt at a if/then was 500.
I got help on another site to redo it (new code shown here), but the new if/else always show the else (Bing).
It was also at this time that I learned that "1,2" only showed 1.
Hoping that this site is far more friendly than StackOverflow.
.
Edited by kpmedia, 17 December 2014 - 07:22 PM. I would like to know what is the best method (the most intelligent and comfortable for users) to paginate a result table with PHP/MySQL? Provided I have a result set of 100 pages. Method 1: <select> <option>1</option> .... </select> Method 2: Link on Page 1, 2, 3 ... 100 If there is any algorithm with PHP for automated these displays, please demonstrate it. Hi Guys. Hopefully someone can help me with this...New to coding and pretty lost on this. I have a Mysql database which is displaying results to my webpage with no problems. However I would like to be able to add a combo box to my webpage that would update the mysql database results based on the combo box selection. For example if Ford is chosen from the combo box, the webpage would refresh and show all the results for Ford in the webpage. Can someone please help me? Here is the code I have at the moment that works just fine. But results of the database are based on the WHERE statement. Quote <?php $con = mysql_connect("server","database","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("a5525005_cars", $con); $result = mysql_query("SELECT * FROM `cars` WHERE Makel='Ford'"); echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr style=font-family:verdana;font-size:80%;>"; echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>"; echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>'; echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>'; echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>'; echo "</tr>"; } echo "</table>"; mysql_close($con); ?> I am trying to display certain MySQL data based off of what link is clicked. For example.. I want to display all the entries from 2009 when a link for 2009 is clicked; maybe like.. <a href="http://website.com/page?yr=09">2009</a> But I am having trouble doing so.. any help would be great! P.S. The years are in my database with only the last two numbers.. 10, 09, 08, etc. $result = mysql_query("select setlist_id, month, day, year, name, venue, location, label1, set1, set2, set3, encore, notes, photos, recording, poster from $database_table order by setlist_id DESC, year, month, day",$db) or die_now("Could not select setlists"); while($row = mysql_fetch_array($result)) { $the_id = $row["setlist_id"]; $the_month = $row["month"]; $the_day = $row["day"]; $the_year = $row["year"]; $the_name = $row["name"]; $the_venue = $row["venue"]; $the_location = $row["location"]; $the_label1 = $row["label1"]; $the_set1 = $row["set1"]; $the_set2 = $row["set2"]; $the_set3 = $row["set3"]; $the_encore = $row["encore"]; $the_notes = $row["notes"]; $the_photos = $row["photos"]; $the_recording = $row["recording"]; $the_poster = $row["poster"]; echo("<div class='date' id='date' tabindex='0'>" . "$the_month" . "." . "$the_day" . "." . "$the_year" . " - "); echo ($the_name != '') ? "" . "$the_name" . " - " : ''; echo("" . "$the_venue" . " - " . "$the_location" . "</div>"); echo("<div class='set'>" . "$the_label1" . ":<br /></div><div class='list'>" . "$the_set1" . "<br /></div>"); echo ($the_set2 != '') ? "<div class='set'>Set Two:<br /></div><div class='list'>" . "$the_set2" . "<br /></div>" : ''; echo ($the_set3 != '') ? "<div class='set'>Set Three:<br /></div><div class='list'>" . "$the_set3" . "<br /></div>" : ''; echo ($the_encore != '') ? "<div class='set'>Enco <br /></div><div class='list'>" . "$the_encore" . "<br /></sdiv>" : ''; echo("<div class='line'></div>"); echo nl2br("<div class='notes'>" . "$the_notes" . "</div>"); } When I replace the $result line with... $result = mysql_query("SELECT * FROM `$database_table` WHERE `year` = '09' order by setlist_id DESC, year, month, day",$db) or die_now("Could not select shows");... I get all the results from just 2009, which is what I want, but I want the results to depend on what link has been clicked. I hope I am making sense. Hi All, Below is my code to add the two different query results into one. This is working fine when both the queries have same no.of rows. eg: row1 = 1 2 3 (Query1) row2 = 3 5 5 (Query2) o/p: 4 7 8 Let's say, I have few rows which are not exactly matched with first query. eg: row1 = 1 2 3 2 (Query1) row2 = 3 empty empty 5 (Query2) o/p : 4 2 3 7 (I want the o/p to be like this) empty means there is no data from the second query. In my while, && working fine when the 2 queries have same no.of rows. while (($row1 = mysql_fetch_assoc($rs1)) && ($row2 = mysql_fetch_assoc($rs2))) { $strHtml .= "<tr>"; $strHtml .= "<td align=center colspan=3>".($row1['Calls']+$row2['Calls'])."</td>"; $strHtml .= "<td align=center colspan=3>".($row1['actual_duration(min)A']+$row2['actual_duration(min)A'])."</td>"; $strHtml .= "<td align=center colspan=3>".($row1['call_usage']+$row2['call_usage'])."</td>"; $strHtml .= "<td align=center colspan=3>".($row1['disconnection_charge']+$row2['disconnection_charge'])."</td>"; $strHtml .= "<td align=center colspan=3>".($row1['total_revenue']+$row2['total_revenue'])."</td>"; $strHtml .= "</tr>"; } Is the while loop i am using correct or there is any other better solution for this? please help me, Thanks in advance. I am trying to join two tables but I am producing no results. I'm convinced that I have entered everything correctly, but clearly I haven't? Can anyone spot my mistake?
<?php
require 'core/memberinit.php';
$member = new Member();
include 'timeago.php';
$memberid = $member->data() ->id;
if(isset($_GET['q'])) {
$q = html_entity_decode($_GET['q']);
$query2 = DB::getInstance()->query("SELECT c.id c.time as time, c.message as message, c.removed1, c.removed2,
m1.member_first_name as firstname1, m1.member_last_name as lastname1, m1.username as username1,
m2.member_first_name as firstname2, m2.member_last_name as lastname2, m2.username as username2
FROM conversation c
LEFT JOIN members m1 ON c.member1=m1.id
LEFT JOIN members m2 ON c.member2=m2.id
WHERE (c.id=$q OR c.originalid=$q)
AND ((c.member1=$memberid AND c.removed1='No')
OR (c.member2=$memberid AND c.removed2='No'))
ORDER BY c.id");
foreach ($query2->results() as $result2)
{
echo $result2->firstname1;
echo $result2->lastname1;
echo $result2->username1;
echo timeAgo(strtotime($result2->time));
echo $result2->message;
echo $result2->firstname2;
echo $result2->lastname2;
echo $result2->username2;
}
?>
Edited by MartynLearnsPHP, 23 November 2014 - 12:55 PM. Hi I have got results being displayed after clicking the search button in a form on my home page but it brings up all the results which is ok but how do I get onlt the results a user searches for for example a location or property type etc as its for a property website The coding is below for the results page Also sorry how do I add a background image to the php page, I tried using css but wouldn't work Code: [Select] <style type="text/css"> body {background-image:url('images/greybgone.png');} </style> <?php mysql_connect ("2up2downhomes.com.mysql", "2up2downhomes_c","mD8GsJKQ") or die (mysql_error()); mysql_select_db ("2up2downhomes_c"); echo $_POST['term']; $sql = mysql_query("select * from properties where typeProperty like '%$term%' or location like '%$term%'"); while ($row = mysql_fetch_array($sql)){ echo 'Type of Property: '.$row['typeProperty']; echo '<br/> Number of Bedrooms: '.$row['bedrooms']; echo '<br/> Number of Bathrooms: '.$row['bathrooms']; echo '<br/> Garden: '.$row['garden']; echo '<br/> Description: '.$row['description']; echo '<br/> Price: '.$row['price']; echo '<br/> Location: '.$row['location']; echo '<br/> Image: '.$row['image']; echo '<br/><br/>'; } ?> I'm having a weird issue in which i'm trying to pull rows from a table based on the contents of specific fields. My query strings a $ms = ("SELECT * FROM games WHERE game = 'test' AND playername = '$test' OR opponentname = '$test' ORDER BY playdate DESC LIMIT 25") or die ('Error: '.mysql_error ()); Which works fine until later I try to use: $ms2 = ("SELECT * FROM games WHERE game = 'test2' AND playername = '$test' OR opponentname = '$test' ORDER BY playdate DESC LIMIT 25") or die ('Error: '.mysql_error ()); What is weird is on $ms2 I found if I change it to: $ms2 = ("SELECT * FROM games WHERE playername = '$test' OR opponentname = '$test' AND game = 'test2' ORDER BY playdate DESC LIMIT 25") or die ('Error: '.mysql_error ()); It works... on one page, on the rest neither line works it just pulls every row in the table that fit one of the two name fields. Has anyone experienced anything like this or know why or a fix for this? Thanks! |
|||||||