PHP - Layout Mysql Query In Separate Tables Based On Value In Column

Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run.
(Table)
qc_reports

(Fields)
id
report_date
report_lot_number    
report_po
report_supplier    
report_buyer    
report_inspectedby    
report_pulptemprange    
report_carrierconditions
report_supplierclaim    
report_carrierclaim    
report_temprecorder    
report_temprange_N    
report_temprange_M    
report_temprange_B    
report_suppliercontact
report_contactedby    
report_time    
report_comments

(Table)
qc_lots

(Fields)
id
report_id
lot_temprange    
lot_commodity    
lot_rpcs    
lot_brand    
lot_terms    
lot_cases    
lot_orgn    
lot_estnum
lot_avgnum

This is the query that I'm trying to do.

Code: [Select]
<?php
$sql = "SELECT * FROM qc_reports, qc_lots WHERE  ";
if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND ";
if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND ";
if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND ";
if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND ";
if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND ";
if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND ";
 if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND ";
if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND ";

$sql = substr($sql, 0, -4);

$query = mysql_query($sql);


$numrows = mysql_num_rows($query);
?>
RESULTS - <?php echo $numrows; ?>
<hr>
<table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td>&nbsp;</td></tr>
<tr>
<td>&nbsp;</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($query))
{
$id = stripslashes($row['id']);
$report_lot_number = stripslashes($row['report_lot_number']);
$report_po = stripslashes($row['report_po']);
$report_date = stripslashes($row['report_date']);

echo "<tr>
<td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td>
</tr>";
}
echo '</table><br><br><br><hr><br><br>';

}

?>
All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table.

I'm stuck and need some guidance. Can someone help please?

I've got a table with 5 columns that shows appointments for Monday through Friday, but I'm using 5 separate queries, one for each column, for each corresponding day because i cannot figure out how to query the whole week and only put the appointments in the column they belong. Thank you for any help.

I have a photo album style gallery to build
and
i'm finding it dificult to list all the table names (these are names of photo albums) and then enter the data into a seperate query for each album name (these will change often so i cant keep updating the file as normal. this will then post all the data to the xml file and show the set of photos in the individual albums in a flash file.
can anyone help me where im going wrong at all?



<?php
$dbname = 'cablard';

if (!mysql_connect('localhost', 'cablard', '')) {
    echo 'Could not connect to mysql';
    exit;
}

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
}

mysql_free_result($result);



$query = "SELECT * FROM photo ORDER BY id DESC"; 
$result2 = mysql_query ($query)  or die ("Error in query: $query. ".mysql_error());

   
   while ($row = mysql_fetch_array($result2))  
   {                                       
         echo  "
<image>
<date>".$row['date']."</date>
<title>".$row['title']."</title>
<desc>".$row['description']."</desc>
<thumb>".$row['thumb']."</thumb>
<img>".$row['image']."</img>
</image>

";


   }                      
?>


Thanks
James

I've got a table (tbl_items) that holds a number of items and the individual information for each. Within 'tbl_items' I also have a row labeled 'category_id' in which each item is given a number (ex: 2).

I've got a separate table (tbl_items_categories) that only has 2 rows: 'category_id' and 'category_name'.

I wasn't sure if a JOIN method would be best for this but what would be the best approach to list all items and within each item, also echo out the name of the category they belong to and not the category_id number?

First, here's some code:

Code: [Select]
$result = mysql_query("SELECT standard FROM thestandards WHERE id={$theSearch}", $connection);
if (!$result) {
     die("Database connection failed: " . mysql_error());
}

while ($row = mysql_fetch_array($result)) {
     $query = $row[0];
}

// echo $testing = nl2br($query);

$subStrings = explode('\n', $query);

echo $subStrings[0] . "<br />";   // outputs entire query
echo $subStrings[1] . "<br />";   // undefined offset
echo $subStrings[2] . "<br />";   // undefined offset
echo $subStrings[3] . "<br />";   // undefined offset   
So, the data I'm retrieving from the database is several small paragraphs.  I want to take these paragraphs, separate them, and put them into an array.  I tried using the explode function with the newline char, but for some reason it doesn't work.  I can get it to work if I want to go explicitly add "\n"s in the database everywhere, but that just doesn't seem practical.  nl2br doesn't work for what I need, but I find it interesting that this function is somehow able to "see" all the newlines in the query, whereas the explode function cannot.  Explode is really what I need, but I've tried '\n', '\r', '\r\n' and nothing works.  Thanks for the help.

Hi, I'm new to the forum so this could go in the MySQL section but I'm not sure.

I am trying to make a page that will list all records from a column in HTML table and have a delete button to remove a specific record. I have got to the part where I have listed the records in a table.
Note: Only records from a specific column ('links') are printed.

Here is the code:
Code: [Select]
$con = mysql_connect("localhost","***","***");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("***", $con);

$result = mysql_query("SELECT * FROM main");

echo "<table border='1'>
<tr>
<th>Current links</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['links'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
How would I go about adding a delete button next to each record to delete that specific record?

Thanks for any help.

Hi guys, PHP advanced amateur and PHPFreaks newbie here. I have a problem with some code I've written. What the code is supposed to do, is take in 15 records in a single MySQL table column, and then print them out in a format like this:

1 2 3
4 5 6
7 8 9

so on so on. Here is the page in question: http://robbieduggan.com/strawberry-interactive/portfolio-web-design.php

Now the problem is, you can see 14 images there, but it seems to be negating the first one. Its always whatever row 0 is, because if I change the SORT BY attrabute in the code below, it's a different one that is missing.

Here is my code to get the data:

mysql_select_db($database_strawberry, $strawberry);
$query_rsProjects = "SELECT page_unique_identity, clean_title, thumbnail FROM tblprojects ORDER BY `timestamp` DESC";
$rsProjects = mysql_query($query_rsProjects, $strawberry) or die(mysql_error());
$row_rsProjects = mysql_fetch_assoc($rsProjects);
$totalRows_rsProjects = mysql_num_rows($rsProjects);


And here is the code to create the table:


echo "<table width='100%' border='0' cellspacing='5' cellpadding='5'>";
for($i = 0; $i < $totalRows_rsProjects; $i++) {
$row = mysql_fetch_array($rsProjects);
if($i % $columns == 0) {
echo "<tr>";
}
echo "<td>";
if($row['page_unique_identity'] != NULL){
echo "<a href='website-details.php?project=".$row['page_unique_identity']."'><img src='portfolio/".$row['thumbnail']."' alt=".$row['clean_title']." width='267' height='162' /></a>";
}
echo "</td>";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $totalRows_rsProjects) {
echo "</tr>";
}
}


Thanks in advance for any help

I'm wondering how to do this with javascript for a table that i have made in php and html.

I want to hide the row for the table based on the value inside my first column for each row of the table.

Like if column 1 has "apple" inside it, then the row would not be shown.
I'm pretty sure this can be done in javascript but i'm not a coding guy, so any help would be appreciated!

My table.php file that has the table i want to do this for:
Code: [Select]
<script language="javascript">

imageX1='plus';
imageX2='plus';
imageX3='plus';

function toggleDisplay(e){
imgX="imagePM"+e;
tableX="table"+e;
imageX="imageX"+e;
tableLink="tableHref"+e;
imageXval=eval("imageX"+e);
element = document.getElementById(tableX).style;
 if (element.display=='none') {element.display='block';}
 else {element.display='none';}
 if (imageXval=='plus') {document.getElementById(imgX).src='http://www.pvpstreams.com/icons/wowicon.png';eval("imageX"+e+"='minus';");document.getElementById(tableLink).title='Hide WoW';}
 else {document.getElementById(imgX).src='http://www.pvpstreams.com/icons/wowicon.png';eval("imageX"+e+"='plus';");document.getElementById(tableLink).title='Show WoW';}
}
</script>

<?php
$username="user";
$password="password";
$tmp="database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($tmp) or die( "Unable to select database");
$query="SELECT * FROM users";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table class="sortable" id="wow" border="1" cellpadding="0" cellspacing="0" style="margin-left:auto;margin-right:auto;text-align:left">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Game</font></th>
<th><font face="Arial, Helvetica, sans-serif">Character</font></th>
<th><font face="Arial, Helvetica, sans-serif">Rating</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/xfirelogo.png">&nbsp;Status</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/xfirelogo.png">&nbsp;Link</font></th>
<th><font face="Arial, Helvetica, sans-serif">Realm</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/livestreamlogo.png">&nbsp;Status</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/livestreamlogo.png">&nbsp;Link</font></th>
<th><font face="Arial, Helvetica, sans-serif">Team&nbsp;Setup</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"wowcharacter");
$f2=mysql_result($result,$i,"username");
$f3=mysql_result($result,$i,"livestream");
$f4=mysql_result($result,$i,"wowserver");

$f5=mysql_result($result,$i,"wowplayer1");
$f6=mysql_result($result,$i,"wowplayer2");
$f9=mysql_result($result,$i,"wowplayer3");

$f7=mysql_result($result,$i,"wowrealm");
$f8=mysql_result($result,$i,"xfire");
$f10=mysql_result($result,$i,"xfirecheck");
$f11=mysql_result($result,$i,"wowrating");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<center><img src=http://www.pvpstreams.com/table-images/wowtable.png>"; ?></center></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://$f4.battle.net/wow/en/character/$f7/$f1/simple>$f1</a>"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$f10"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://www.pvpstreams.com/xfire.php?user=$f2>$f8</a>"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php include("livestreamcheck.php"); ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://www.pvpstreams.com/livestream.php?user=$f2>$f3</a>"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<img src=http://www.pvpstreams.com/wclassicons/$f5.png><img src=http://www.pvpstreams.com/wclassicons/$f6.png><img src=http://www.pvpstreams.com/wclassicons/$f9.png>" ?></font></td>
</tr>


<?php
$i++;
}
?>
</table>

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

Dear All

I want to display my data in tabular columns with the headings of table  like 00:06-00:08,00:08-00:10,00:10:00:12:00........22:00-24:00.i.e.time
In database I have two columns one containing value and other containing time when this reading was done.
I could retrieve data from my mysql table in php and look at time.
I want to know how could I insert the value to the correct column of table based on the time.
e.g If I have record in table in database say containing value 09:45 and 5.
I want this value to go to the column heading 00:08-00:10 and same thing for the other values.

I hope I am clear with the problem definition but if you need more info.Please let me know.

Thanks,
elp

Hi everyone, I'm having real problems trying to retrieve database records in a 3 column layout, I got there eventually with a huge amount of help.

Unfortunately the code will only display records that are divisable by by three; for example for a database table that has 45 records there is no problem, as all records can be displayed in a 3 column layout. However if the table contains 47 records it won't display the 2 odd records.  My php skills are limited so I need all the help I can get.

I'm a bit desperate to sort this out for a project I'm doing, any help would be greatly appreciated - here is the php code:


$total = count($records);
  $nocol = 3;
  $norows = $total / $nocol;

for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if
       ($col == 1) {
       $cell += $i;
       echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
       echo $records[$cell - 1]['ret_add1'].'<br />';
       echo $records[$cell - 1]['ret_add2'].'<br />';
       echo $records[$cell - 1]['ret_town'].'<br />';
       echo $records[$cell - 1]['ret_county'].'<br />';
       echo $records[$cell - 1]['ret_pcode'].'<br />';
       echo $records[$cell - 1]['ret_phone'].'<br />';
       echo $records[$cell - 1]['ret_email'].'<br />';
       echo $records[$cell - 1]['ret_web'].'<br />';
       } else {
        $cell += $norows;
       echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
       echo $records[$cell - 1]['ret_add1'].'<br />';
       echo $records[$cell - 1]['ret_add2'].'<br />';
       echo $records[$cell - 1]['ret_town'].'<br />';
       echo $records[$cell - 1]['ret_county'].'<br />';
       echo $records[$cell - 1]['ret_pcode'].'<br />';
       echo $records[$cell - 1]['ret_phone'].'<br />';
       echo $records[$cell - 1]['ret_email'].'<br />';
       echo $records[$cell - 1]['ret_web'].'<br />';
       
       }
       echo"</td>";
    }
      echo"</tr>";
} 


I'm also trying to paginate the results, is this actually possible when using a three column layout?  I look forward to any suggestions.

I have been trying to work out how to get my results into a 3 column layout using css and not using tables in any way.

I found the code for tables:

echo '<table>'; 

$counter = 0; 
$cells_per_row = 3; 
while($row=mysql_fetch_array($result)) 
{ 
    $counter++; 
    if(($counter % $cells_per_row) == 1)  { echo '<tr>'; } 
    echo '<td>' . (whatever you echo from your $row array) . '</td>'; 
    if(($counter %  $cells_per_row) == 0) { echo '</tr>'; } 
} 
// just in case we haven't closed the last row 
// this would happen if our result set isn't divisible by $cells_per_row 
if(($counter % $cells_per_row) != 0) { echo '</tr>'; } 

echo '</table>'; 


How Can I adapt this or how should I use divs here?
I am fine with the css code, just need to work out how to get the 3 column layout correct in the loop.

I have 11 news then i want to display like

first column need only one news

second column need 5 news

third column need the rest of 5 news

I tried this but no luck, it shows first column and second column and the rest outside column

 

                    $rows = array(
                        'Title1',
                        'Title2',
                        'Title3',
                        'Title4',
                        'Title5',
                        'Title6',
                        'Title7',
                        'Title8',
                        'Title9',
                        'Title10',
                        'Title11',
                    );

                    $total_rows = count($rows);
                    $total_cols  = $total_rows - 1;// remove first one for the first column
                    $left_column = ceil($total_cols / 2);
                    $right_column = $total_cols - $left_column;

                    $i = 0;
                    foreach ($rows as $row) {
                        $i++;
                        if ($i == 1) {
                            $class = "primary_post";
                            echo "<div class='col-md-4 main'>";
                        } elseif ($i <= $left_column) {
                            $class = "other_post";
                            echo "<div class='col-md-4 left'>";
                        } elseif ($i == $right_column) {
                            $class = "other_post";
                            echo "<div class='col-md-4 right'>";
                        } else {
                            $class = "other_post";
                        }

                        echo "<div class='card {$class}'>$i</div>";
                        if ($i == 1 || $i == $left_column || $i == $right_column) {
                            echo "</div>";
                        } else {
                            echo "";
                        }
                    }
                    echo "</div>";

 

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1