PHP - Add A Numbered Column To Sql Results?

Hey all, first post to this site. I'm somewhat new to PHP, so bear with me - this might be an easy question, it might not be. Just looking for a little help.

Basically, I have a query which takes commands from a form through AJAX (day, time, time1). The query executes a wildcard on a table named sip_data, searches for linked $id (so if $id=3, for example in multiple tables, it spits out the $name, $zip in a div).

Here's the code:

Code: [Select]
<?php
$dbhost = "localhost";
$dbuser = "";
$dbpass = "";
$dbname = "";

//Connect to MySQL Server
$link = mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String
$id = $_GET['id'];
$name = $_GET['name'];
$zip = $_GET['zip'];
$server_url = $_GET['server_url'];
$day = $_GET['day'];
$time = $_GET['time'];
$time1 = $_GET['time1'];

// Escape User Input to help prevent SQL Injection
$id = mysql_real_escape_string($id);
$name = mysql_real_escape_string($name);
$zip = mysql_real_escape_string($zip);
$server_url = mysql_real_escape_string($server_url);
$day = mysql_real_escape_string($day);
$time = mysql_real_escape_string($time);
$time1 = mysql_real_escape_string($time1);
//build query

$query = "SELECT * FROM $day,sip_data WHERE $day.id=sip_data.id AND $day$time<=>$day$time1 ORDER BY zip ASC";

//Execute query
$qry_result = mysql_query($query) or die(('No Results'));

//Build Result String
while($row = mysql_fetch_array($qry_result))

{
echo "<table id=query_result align=left>";
echo "<tr>";
    if($zip = $row[zip]); 
echo "<th><b>$row[zip]</b></th>";
echo "</tr>";
echo "<tr>";
echo "<td><a href=$row[server_url] rel=ajaxDiv>$row[name]</a></td>";
echo "</tr>";
echo "</table>";
}
echo $display_string;




?>





Basically, my issue with this is that some of the results within the sip_data DB will have zip codes that repeat. The current code prints out each results with the zip code and name - here's an image of a sample result:



What I would like to do is avoid repeating the zip code and just group the results under each zip code, kinda like this:

55408           55412        55423
---------     ------------    ----------
example      example       example
example                         example
example

Any help would be greatly appreciated. I feel like I'm so close to the answer, but just need a little guidance. Who knows, I might be way off.

Thanks in advance!

Hey guys I need some help, Im trying to generate a numbered list by using i++ and an if statement but I cant seem to get the values to start over to 0, here is a look at my code
Code: [Select]
$make = "SELECT * FROM category_description ORDER BY category_id ASC LIMIT 1000";
$makeresult = mysql_query($make);
while($make1 = mysql_fetch_array($makeresult)){
  $parent = "SELECT * FROM category WHERE parent_id = {$make1['category_id']} LIMIT 1000";
$parentresult = mysql_query($parent);
while($parent1 = mysql_fetch_array($parentresult)){
    if(($make1['category_id'] == $parent1['parent_id']) AND ($make1['name'] == $make1['name'])){
 $i += 1;
  $count = $i;
 echo "[". $count . "]" . " " . $parent1['category_id'] . " " . $make1['category_id'] . " " . $parent1['parent_id'] . " " . $make1['name'] . "<br />";
Basically the output comes out like this:
Code: [Select]
[1] 100 10 10 Acura
[2] 101 10 10 Acura
[3] 102 10 10 Acura
[4] 103 10 10 Acura
[5] 104 10 10 Acura
[6] 105 10 10 Acura
[7] 106 10 10 Acura
[8] 107 10 10 Acura
[9] 108 10 10 Acura
[10] 109 10 10 Acura
[11] 110 10 10 Acura
[12] 111 10 10 Acura
[13] 112 10 10 Acura
[14] 113 10 10 Acura
[15] 114 10 10 Acura
[16] 115 10 10 Acura
[17] 116 10 10 Acura
[18] 117 10 10 Acura
[19] 118 10 10 Acura
[20] 119 10 10 Acura
[21] 120 10 10 Acura
[22] 121 10 10 Acura
[23] 122 10 10 Acura
[24] 123 10 10 Acura
[25] 124 10 10 Acura
[26] 125 10 10 Acura
[27] 126 11 11 Audi
[28] 127 11 11 Audi
[29] 128 11 11 Audi
[30] 129 11 11 Audi
[31] 130 11 11 Audi
[32] 131 11 11 Audi
[33] 132 11 11 Audi
[34] 133 11 11 Audi
[35] 134 11 11 Audi
[36] 135 11 11 Audi
[37] 136 11 11 Audi
[38] 137 11 11 Audi
But I need it to come out like this, so basically if Acura = Acura generate 1,2,3,4 etc and if Audi = Audi generate 1,2,3,4......Im still somewhat new to this would really appreciate the help if someone can point me in the right direction:
Code: [Select]
[1] 100 10 10 Acura
[2] 101 10 10 Acura
[3] 102 10 10 Acura
[4] 103 10 10 Acura
[5] 104 10 10 Acura
[6] 105 10 10 Acura
[7] 106 10 10 Acura
[8] 107 10 10 Acura
[9] 108 10 10 Acura
[10] 109 10 10 Acura
[11] 110 10 10 Acura
[12] 111 10 10 Acura
[13] 112 10 10 Acura
[14] 113 10 10 Acura
[15] 114 10 10 Acura
[16] 115 10 10 Acura
[17] 116 10 10 Acura
[18] 117 10 10 Acura
[19] 118 10 10 Acura
[20] 119 10 10 Acura
[21] 120 10 10 Acura
[22] 121 10 10 Acura
[23] 122 10 10 Acura
[24] 123 10 10 Acura
[25] 124 10 10 Acura
[26] 125 10 10 Acura
[1] 126 11 11 Audi
[2] 127 11 11 Audi
[3] 128 11 11 Audi
[4] 129 11 11 Audi
[5] 130 11 11 Audi
[6] 131 11 11 Audi
[7] 132 11 11 Audi
[8] 133 11 11 Audi
[9] 134 11 11 Audi
[10] 135 11 11 Audi
[11] 136 11 11 Audi
[12] 137 11 11 Audi

Hi all, this is my first post on this site.  I've built a few amateur sites in the past but am keen to utilize php combined with mysql for my current project.  However, I have little knowledge of php and am just picking it up as I go (same way I've done for all my computer knowledge)

What I'm looking for may sound simple, but I really don't know where to start and I'm hoping there are some kind souls here that will lend a hand.

SQL Database
Name Score
John 42
Peter 46
Michael 56
Angela 46
(many rows like this)

I am looking to output on my page a table of the top ten scores like this.  However it's not quite that simple, should there be a tie on any score I want it to appear like so:
1. Michael 56
2. Angela 46
    Peter 46
4. John 42
(onwards to 10)

I want this to run from positions 1 thru 10, but should the 10th score in the database be equal to the 11th (12th, 13th etc) I would like them displayed too.  For example:
9. Rafael 31
10. Sandy 29
      Bernard 29
      Jackie 29


Is this possible or would I be better off just sorting the info in excel and hard coding the info everytime?


Hope somebody can help.

Thanks in advance.

Hello,

I'm generating a list of questions from a database, say $questions.  I'd like to number the questions AFTER I load them from the database (I know that I could enter a number associated with them in the database, but it could get tricky, as the questions will continually be updated and deleted).  My question, then, is how can a create a new array which associates each question with a number.

Thank you!

My Php Buddies,

I have mysql tbl columns these:

id: 
date_&_time: 
account_activation_code: 
account_activation_status: 
id_video_verification_status: 
id_verification_video_file_url: 
username: 
password: 
primary_domain: 
primary_website_email: 
sponsor_username: 
 

Now, I want to display their row data by excluded a few columns.

Want to exclude these columns:

date_&_time

account_activation_code

account_activation_status

id_verification_video_file_url

password

 

So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:

Id: 1
Id Video Verification Status: 1
Username: requinix
Primary_domain: requinix.majesty
Primary Website Email: requeen@requinix.majesty
Sponsor Username: phpsane
Recruits Number: 2

 

For your convenience only PART 1 works. Need help on Part 2

My attempted code:

 

	PART 1
	<?php 
	// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
	// Query to get columns from table
$query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'");
	while($row = $query->fetch_assoc()){
    $result[] = $row;
}
	// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');
	foreach ($columnArr as $value) { 
echo "<b>$value</b>: ";?><br><?php 
} 
	?>
	 
	PART 2
	<?php 
	//Display User Account Details 
echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br> 
<?php 
$excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password"); 
foreach ($excluded_columns as $value2) 
{    
    echo "Excluded Column: <b>$value2</b><br>";
}
foreach ($columnArr as $value) 
{ 
    
    if($value != "$value2")
    { 
        $label = str_replace("_"," ","$value"); 
        $label = ucwords("$label"); 
        //echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php 
        echo "<b>$label</b>: "; echo "${$value}";?><br><?php 
    }
} 
?>
	

PROBLEM: Columns from the excluded list still get displayed.

Edited November 19, 2018 by phpsane

Hellow, i need help please, writing code and it doesn't work. please help...   Here it is

WHERE start_date BETWEEN 'start_date".strtotime('-3 day')."' AND 'start_date'";

without this code everithing works fine   Thank you

Hi, I'm trying to get the last number from a column of numbers, add 1 to it and use this to insert a new value in the column.
The column is not the primary key, indexed, or auto insert, it's just a plain old int field.

$newid = mysql_query('SELECT IDCOL FROM prods ORDER BY IDCOL DESC LIMIT 1');

// the above just doesn't work--it returns 0, there are over 1200 products--I'm going nuts over this.

//$newid += 1; I've commented this out, it works though (in that it adds 1 to 0)

   $sql = mysql_query("INSERT INTO prods (IDCOL , Prod_Code, blah... blah..)
        VALUES('$newid','$prod_code','blah... blah...')") or die (mysql_error()); //this works (but inserts 0 as the newID)

You'd think this would be so easy.
Thanks
Steve

Hey,

I've got a query in which a variable is interpreted as a column and I don't why this is caused.


$upgrade_time_sql = "SELECT * FROM todo_upgrades WHERE profile_id = ".$profile_id." AND level = ".$profile_data['level2_'.$show.'']." AND type = ".$show."";
$upgrade_time_res = mysql_query($upgrade_time_sql) or die (mysql_error());


$show is filled with the content "storage"

The mysql_error is

"Unknown column 'storage' in 'where clause'"

Thanks for helping.

I fairly new to PHP. This seems very obvious but I cannot find an answer.

O programmer friend of mine said that I should never store a calculated value in a table because it was redundant. SO I have a very simple shopping cart. In the confirmation email, I ask the database for the line items of the order. So I'm pulling the quantity and price from the database. In the email I created a var called $ext which is $price * $disc which works.  I cannot figure out how to total $ext.

I created a var called $subtotal. Heres the code.

$result = mysql_query("SELECT * FROM order_detail, products
WHERE $ordid = orderid AND
order_detail.productid = products.serial");

while($row = mysql_fetch_array($result))
{
   $prodid = $row['productid'];
   $qty = $row['quantity'];
   $price = $row['price'];
   $prodname = $row['name'];
   $proddesc = $row['description'];
   $ext = $qty * $price;
   $subtotal = 0;
   $subtotal = ($subtotal + $ext);
}

Any help would be appreciated. 

How can i get the last row data from a certain column?

hi guys,
i made this code list US states in six columns....the problem is that it lists it across rather than down

it lists it like thi

Alabama     Alaska     Arizona    blah1    blah2
blah3            blah4       blah5      blah6

when I want it to list like this

Alabama   blah2 
Alaska      blah3
Arizona    blah 4
blah1       blah5

but not just two columns....I want 4 columns

here is my code


$query = mysql_query("SELECT * FROM states ORDER BY name ASC");
echo '<center><table width="250" border="0" cellpadding="5" cellspacing="0">
<tr>';

while($show = mysql_fetch_array($query))
{

$column = 1+$column;

if($column == 6)
{
$column = '1';
$break = '</td></tr>';
}
else
{
$break = '';
}
echo '<td>
<table >
<tr class="style5">
  <td width="50%" align="left" height="23" nowrap>'.$show['name'].'</td>
  <td height="23" valign="middle" > 
    </td>
</tr>
</table>
'.$break.'';
}


echo '</table></center>';

I am generating a csv dynamically and getting it downloaded from php but after download the csv only contain the data from database not the heading or the column name of the database. 
example: my table contain "users" as column name and it has 4 rows like "ram"  "sam"  "hari"  "sita". then after download of csv, the csv is showing only ram, sam, hari sita not the column name users. How to do that? because it is difficult to know who the ram sam are,, are they any vegetables or users

I want to echo the contents of column 4 for my username only, and it doesn't show anything. I can do if ($memberQuery->fetchColumn(4) < 2)() though.

Code: [Select]
$host = 'localhost';
$database = '';
$db_username = '';
$db_password = '';

if (($db = new PDO("mysql:host=$host;dbname=$database", $db_username, $db_password)) == NULL) {
echo $db->getMessage();
}

$memberQuery = $db->prepare("SELECT * FROM members WHERE username=:username");
$memberQuery->execute(array(':username' => $_SESSION['username']));
$result = $memberQuery->fetchColumn(4);
echo "$result";

Hi

Okay, after successfully getting a form to input data to a MySQL database, I'm now trying to get the data back out by searching.

Search form code:

Code: [Select]
<form name="search" method="post" action="process_search.php">
 Seach for: <input type="text" name="find" /> in
 <select name="columns">
 <option value="">Please choose one:</option>
 <option value="status">Status</option>
 <option value="date">Date</option>
 <option value="species">Species</option>
 <option value="breed">Breed</option>
 <option value="sex">Sex</option>
 <option value="primary_colour">Primary Colour</option>
 <option value="colour">Colour</option>
 <option value="distinctive_traits">Distinctive Traits</option>
 <option value="fur_length">Fur Length</option>
 <option value="age">Age</option>
 <option value="desexed">Desexed</option>
 <option value="microchipped">Microchipped</option>
 <option value="suburb">Suburb</option>
 <option value="pound_area">Pound Area</option>
 <option value="contact">Contact</option>
 <option value="link">Link</option>
 </Select>
 <input type="hidden" name="searching" value="yes" />
 <input type="submit" name="search" value="Search" />
 </form>

All good there!  Form displays how I want it to, and submits correctly.

The bit I'm struggling with is the process_search.php side of things.  This is what I have (note that i'm trying to get the results into a formatted table and I'd like more results if the match is in more than one column):

Code: [Select]
<?
 //Here we display stuff if they have submitted the form
 if ($searching =="yes")
 {
 echo "<h2>Results</h2><p>";
 
 //If they stuffed up and didn't search for anything, we show them this
 if ($find == "")
 {
 echo "<p>Oh, Bianca, you need to enter SOMETHING to search!";
 exit;
 }
 
 //If everything is all good, we connect to the database
 mysql_connect("localhost", "******", "******") or die(mysql_error());
 mysql_select_db("******") or die(mysql_error());
 
//Let's not forget the register globals off crap
$status = $_POST['status'];
$date = $_POST['date'];
$species = $_POST['species'];
$breed = $_POST['breed'];
$sex = $_POST['sex'];
$primary_colour = $_POST['primary_colour'];
$colour = $_POST['colour'];
$distinctive_traits = $_POST['distinctive_traits'];
$fur_length = $_POST['fur_length'];
$age = $_POST['age'];
$desexed = $_POST['desexed'];
$microchipped = $_POST['microchipped'];
$suburb = $_POST['suburb'];
$pound_area = $_POST['pound_area'];
$contact = $_POST['contact'];
$link = $_POST['link'];
$columns = $_POST['columns'];
 
 // We perform a bit of filtering
 $find = strtoupper($find);
 $find = strip_tags($find);
 $find = trim ($find);
 
 //Now we search for our search term, in the field the user specified
 $data = mysql_query("SELECT * FROM animal_info WHERE upper($columns) LIKE'%$find%'");
 
 //And we display the results
 where($result = mysql_fetch_array( $data ))
 {
    echo "<tr>";
    echo "<td>".$row['status']."</td>";
    echo "<td>".$row['date']."</td>";
    echo "<td>".$row['species']."</td>";
    echo "<td>".$row['breed']."</td>";
echo "<td>".$row['sex']."</td>";
echo "<td>".$row['primary_colour']."</td>";
echo "<td>".$row['colour']."</td>";
echo "<td>".$row['distinctive_traits']."</td>";
echo "<td>".$row['fur_length']."</td>";
echo "<td>".$row['age']."</td>";
echo "<td>".$row['desexed']."</td>";
echo "<td>".$row['microchipped']."</td>";
echo "<td>".$row['suburb']."</td>";
echo "<td>".$row['pound_area']."</td>";
echo "<td>".$row['contact']."</td>";
echo "<td>".$row['link']."</td>";
    echo "</tr>";
 }
 else
 {
echo "ERROR: ".mysql_error();
}
 //This counts the number or results - and if there wasn't any it gives them a little message explaining that
 $anymatches=mysql_num_rows($data);
 if ($anymatches == 0)
 {
 echo "Nope, couldn't find anything here!  Maybe refine your search criteria?<br><br>";
 }
 
 //And we remind them what they searched for
 echo "<b>Searched For:</b> " .$find;
 }
 ?>
Trouble with this is that I'm not getting any errors OR results!

ANY help would be appreciated HUGELY!

Cheers,

Dave