PHP - Mysql Table Column Average

Hi all, I have a table...

id   |   Name       |   Downloads   |
01  |   tom          |    5
02  |   thomas     |    2
03  |   tommy      |    15

I need a button in my page whcih on submit would resent the 'downloads' column so all values are set to 0, Is this possible and how if it is?

Thanks for any advice

have a database with a column of datatype EM (multiple selection, say apples, oranges, grapes)

I want to have a form with checkboxes for those values (apples, oranges, grapes etc) so one can select a breakfast basket type that contains for example, oranges and grapes.

It would pull from the database those baskets with those selection of fruits (specified in the column 'Fruits', which is a EM datatype column. (I have a multiple selection when entering the values for the column.)

Can it be done? Should I make another table with FruitNames?

Here is one of those purely conceptual questions, which involves no code.  I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range.  I have a column in the table where the members' information are stored which records their ages.  Which brings us to the problem.  On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically.  I can't think of any way to automatically update this column on a daily basis.  So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range.  This leads to the second problem.  Imagine there are thousands of users using the website.  At any given instance, there could be hundreds of members, trying to search others based on age.  This means hundreds of users will be updating a single column (the age column) in one table at the same time.  Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses.

I have a kind of search and react on my site. The user can search for a number in a column as below

$search = mysqli_real_escape_string($link, $_POST["query"]);

$query = "SELECT * FROM $table[$i] WHERE oemnr LIKE '".$search."%' ORDER BY model";

 

The code works fine but I want to expand the function to get match for other numbers also. The oemnr column today can be 12345 which shows up if the user type 12.... 

I want to expand the oemnr column as

12345/03124/34713, numbers separated with / or another character.

If the user type 03... or 34... I want to find and show 12345 because it is on the same row and corresponds to the numbers on the same row. Is it possible to do in mysql? In php that kind of operation can be done with the explode function but I don't know how to use with mysql.

Edited February 14 by jigga

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

Hi I am very new to PHP & Mysql. I am trying to insert values into two tables at the same time. One table will insert a single row and the other table will insert multiple records based on user insertion. Everything is working well, but in my second table, 1st Table ID simply insert one time and rest of the values are inserting from 2nd table itself. Now I want to insert the first table's ID Field value (auto-incrementing) to a specific column in the second table (only all last inserted rows).   Ripon.   Below is my Code:

<?php



$con = mysql_connect("localhost","root","aaa");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("ccc", $con);


$PI_No = $_POST['PI_No'];
$PO_No = $_POST['PO_No'];


$qry = "INSERT INTO wm_order_entry (
Order_No,
PI_No,
PO_No) 


VALUES(
NULL,
  '$PI_No',
 '$PO_No')";
$result = @mysql_query($qry);




$val1=$_POST['Size'];
$val2=$_POST['Style'];
$val3=$_POST['Colour'];
$val4=$_POST['Season_Code'];
$val5=$_POST['Dept'];
$val6=$_POST['Sub_Item'];
$val7=$_POST['Item_Desc'];
$val8=$_POST['UPC'];
$val9=$_POST['Qty'];
$N = count($val1);
for($i=0; $i < $N; $i++)
{


$profile_query = "INSERT INTO order_entry(Size,
Style,
Colour,
Season_Code,
Dept,
Sub_Item,
Item_Desc,
UPC,
Qty,
Order_No
) VALUES( '$val1[$i]','$val2[$i]','$val3[$i]','$val4[$i]','$val5[$i]','$val6[$i]','$val7[$i]','$val8[$i]','$val9[$i]',LAST_INSERT_ID())";
$t_query=mysql_query($profile_query);
}
header("location: WMView.php");
mysql_close($con);
?>

Output is attached.

Simple question but I couldn't find any straight forward answers.   I have a customer details table with: customer ID, name, address, email.   I also have an delivery table with delivery ID, customer ID, order ID.   How can I link the customer ID column from the customer table to the delivery table so that when changes occur on the customer table they subsequently alter all other tables?   I know it might use foreign keys but I couldn't find any online resource that explained how to use them properly. Cheers

Is there a way to use php to make an automatic column addition to a table?  like if I had a comment form and want to save the replys in a database, I would want a column for each reply, is there a way to make a new column each time someone submits the form?

Php Buddies,

Just how do you insert an uploaded img via html for ($_POST) to mysql tbl and what type of column should it be ?

Also, how to link the img from the mysql column to a webpage ?

Following html links the img from a folder/directory to a webpage:

<a href="../html-link.htm"><img src="flower.jpg" style="width:82px; height:86px" title="White flower" alt="Flower"></a>

Hello,

I'm having some issues with PHP thinking that the variables that I send it are the actual columns in my database.  First, I pull off Quadratic_Functions and introductory_problem from http://localhost:8888/algebra_book/Chapters/Quadratic_Functions/introductory_problem.php using the code below:

Code: [Select]
$chapter_page =  $_SERVER['PHP_SELF'];
$chapter_page_array = explode('/',$chapter_page);
$size =count($chapter_page_array);
$chapter = $chapter_page_array[$size-2];
$page = $chapter_page_array[$size-1];
$page_array = explode('.', $page);
$page = $page_array[0];
Based on my printing of the variables $chapter and $page I think that it's doing what I want it to do.  I then use the following function:

Code: [Select]
$supplemental_id = getSupplementalId($dbRead,$chapter,$page);
to check out if the there's a supplemental_id for the Quadratic_Function chapter and introductory_problem page name via:

Code: [Select]
function getSupplementalId($read,$user_id,$chapter,$page)
  {
  $sql = "SELECT supplemental_id
  FROM instructors_supplemental
WHERE page_name = $page
AND chapter_name='$chapter";

return $read->fetchRow($sql);
  }
If I stick in actual values, as seen below, the thing runs fine.

Code: [Select]
$sql = "SELECT supplemental_id
  FROM instructors_supplemental
WHERE page_name = 'introductory_problem'
AND chapter_name='Quadratic_Functions'";

But if I run it in the abstract version, with variables for page and chapter name (the first version), I get Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'introductory_problem' in 'where clause'' in...

It's almost as if it thinks that my variables are the names of the columns. 

Any thoughts would be appreciated....

Hi everyone,

I got a bit of code off the web and did some tweaking. I want to use it to export data from a mysql table to excel. I do a website for a friend and have been exporting it myself and emailing it to him. However, I thought it'd be easier if I could just direct him to a link so he could get it whenever.

Anyway, the code below works pretty well...the only problem being that it exports all data to the first column of the excel file. There are commas between all fields, so it is possible to use the 'text to columns' feature in excel to separate teh data into separate columns.

However, if there is a way of fixing the code that would negate the need to have to do this, it would be much appreciated.

Here goes:





<?php

$db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD");
mysql_select_db("MYDATABASE",$db);

$query="SELECT * FROM MYTABLENAME";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$csv_output = "firstname,lastname,emailaddress,crew_name_001"; 
$csv_output .= "\r\n"; 

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");

$firstname = mysql_result($result,$i,"firstname");
$lastname = mysql_result($result,$i,"lastname");
$emailaddress = mysql_result($result,$i,"emailaddress");
$crew_name_001 = mysql_result($result,$i,"crew_name_001");

$csv_output .= "$firstname,$lastname,$emailaddress,$crew_name_001\n";
++$i;
}

$mode="xls";
$type="excel";


header("Content-type: text/x-csv");  

header("Content-disposition: attachment; filename=".date("d-m-Y")."-export.xls");  

print $csv_output;
exit;

?>



Thanks for your time,

Dave

hi,

i'm new in php/mysql. i'm stored student marks values in following format in mysql db table.


id student_code Tamil English Maths Science Social
1                1        100      75       78      88       95
2                2         85      90        88     80      100

But i want to search and display the specific student marks in following format.

id:1

student_code:1

Tamil:100

English:75

Maths:78

Science:88

Social:92

Total:?

Avg:?

please give correct code for this format.

Hi, I'm new here and I should say, I am a raw amateur at PHP and MySQL. All I can do is look at how others do similar things and try and adapt it.

I have a MySQL table: Neil_Details. It has 4 columns: id, number, name, password.

I can enter number and name from a csv file automatically, no problem. id is the Primary Key and will auto_increment.

The column 'number' is the student numbers, name is their Chinese names.

I want to take the column number and generate a password from each number in the column password.  That's all!

Quote

$password = password_hash($number, PASSWORD_DEFAULT);

People tell me, "Don't use their student numbers," but once I know how to do this in principle, I can splice part of the number and Chinese name together somehow and make a more secure password.

I just want to learn how to do this in principle. Then I can do it for each class.

So far I have this, which doesn't seem to work, although I get no errors in /var/log/apache2/error.log

Could you please help me along a bit with some tips, pointers, links?

My index.php first calls '/makePassword/form.html.php'

This is called makePassword.php.

Thanks for any tips!

<?php
        //start PHP session
        session_start();
     
        //check if login form is submitted
        if(isset($_POST['gettable'])){
            //assign variables to post values
            $tablename = $_POST['tablename'];
            
// login to the database dbname=allstudentsdb
        include $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdb.inc.php';
        
        $stmt = $pdo->prepare('SELECT * FROM ' . $tablename . ' WHERE number = :number');
                $stmt->execute(['number' => $number]);
                $password = password_hash($number, PASSWORD_DEFAULT);
                echo 'password is ' . $password;
                $stmt = $pdo->prepare('INSERT INTO ' . $tablename . ' (password) VALUES (:password)');
                try{
                        $stmt->execute(['password' => $password]);
     
                        
                    }
                    catch(PDOException $e){
                        $_SESSION['error'] = $e->getMessage();
                    }
               }
               echo 'Passwords made!';

// the input form to get the mysql table name
               header('location: ' . '/makePassword/form.html.php');
               ?>

 

I have a table where it displays some data. How do I make this Code: [Select]
echo "<td>" . $row['title'] . "</td>"; into a link?

I have a database with one row and about 50 columns, where the inserted data is being displayed on a page. But a lot of the time not all columns are being used. So say only 25 columns have data, there will be 25 blanks spaces with an &#8226; symbol.

Is there a way to ignore to columns that have no data in them? Such as SELECT * from notices WHERE [columns have data]



Code: [Select]
<?php
$html="<table>";
$q="select * from notices";
$r=mysql_query($q);
while($o=mysql_fetch_object($r)){
foreach($o as $key=>$val){
$html.="<tr><td colspan='2' bgcolor='#896B45'><div style='padding:10px;'>&#38;#8226;&nbsp;{$val}<br></div></td></tr>";
}}
$html.="</table>";
echo $html;
?>



CREATE TABLE `notices` (
  `id` int(11) NOT NULL auto_increment,
  `notice1` text,
  `notice2` text,
  `notice3` text,
  `notice4` text,
  `notice5` text,
  `notice6` text,
  `notice7` text,
  `notice8` text,
  `notice9` text,
  `notice10` text,
  `notice11` text,
  `notice12` text,
  `notice13` text,
  `notice14` text,
  `notice15` text,
  `notice16` text,
  `notice17` text,
  `notice18` text,
  `notice19` text,
  `notice20` text,
  `notice21` text,
  `notice22` text,
  `notice23` text,
  `notice24` text,
  `notice25` text,
  `notice26` text,
  `notice27` text,
  `notice28` text,
  `notice29` text,
  `notice30` text,
  `notice31` text,
  `notice32` text,
  `notice33` text,
  `notice34` text,
  `notice35` text,
  `notice36` text,
  `notice37` text,
  `notice38` text,
  `notice39` text,
  `notice40` text,
  `notice41` text,
  `notice42` text,
  `notice43` text,
  `notice44` text,
  `notice45` text,
  `notice46` text,
  `notice47` text,
  `notice48` text,
  `notice49` text,
  `notice50` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

I have an admin page for the posting of the date to the database:
Code: [Select]
<?php 
include_once($_SERVER["DOCUMENT_ROOT"] . "/main.inc.php");
$page_content = db_select("select * from notices");
$pc = $page_content[0];
$ff=$_POST;
if ($ff['submit']) {
$insc="UPDATE notices SET notice1='".$ff['notice1']."' , notice2='".$ff['notice2']."' , notice3='".$ff['notice3']."' , notice4='".$ff['notice4']."' , notice5='".$ff['notice5']."' , notice6='".$ff['notice6']."' , notice7='".$ff['notice7']."' , notice8='".$ff['notice8']."' , notice9='".$ff['notice9']."' , notice10='".$ff['notice10']."' , notice11='".$ff['notice11']."' , notice12='".$ff['notice12']."' , notice13='".$ff['notice13']."' , notice14='".$ff['notice14']."' , notice15='".$ff['notice15']."' , notice16='".$ff['notice16']."' , notice17='".$ff['notice17']."' , notice18='".$ff['notice18']."' , notice19='".$ff['notice19']."' , notice20='".$ff['notice20']."' , notice21='".$ff['notice21']."' , notice22='".$ff['notice22']."' , notice23='".$ff['notice23']."' , notice24='".$ff['notice24']."' , notice25='".$ff['notice25']."' , notice26='".$ff['notice26']."' , notice27='".$ff['notice27']."' , notice28='".$ff['notice28']."' , notice29='".$ff['notice29']."' , notice30='".$ff['notice30']."' , notice31='".$ff['notice31']."' , notice32='".$ff['notice32']."' , notice33='".$ff['notice33']."' , notice34='".$ff['notice34']."' , notice35='".$ff['notice35']."' , notice36='".$ff['notice36']."' , notice37='".$ff['notice37']."' , notice38='".$ff['notice38']."' , notice39='".$ff['notice39']."' , notice40='".$ff['notice40']."' , notice41='".$ff['notice41']."' , notice42='".$ff['notice42']."' , notice43='".$ff['notice43']."' , notice44='".$ff['notice44']."' , notice45='".$ff['notice45']."' , notice46='".$ff['notice46']."' , notice47='".$ff['notice47']."' , notice48='".$ff['notice48']."' , notice49='".$ff['notice49']."' , notice50='".$ff['notice50']."' , id='1' WHERE id='1'";

mysql_query($insc);

}
?>
<html>
<head>
</head>
<body>
 <form name="noticesform" method="post" action="notices_admin.php" enctype="multipart/form-data">
                                   
                                      <textarea name='notice1' cols='110' rows='3' class='input'><?=$pc["notice1"]?>
</textarea>
                                      <textarea name='notice2' cols='110' rows='3' class='input'><?=$pc["notice2"]?>
</textarea>
                                      <textarea name='notice3' cols='110' rows='3' class='input'><?=$pc["notice3"]?>
</textarea>
                                      <textarea name='notice4' cols='110' rows='3' class='input'><?=$pc["notice4"]?>
</textarea>
                                      <textarea name='notice5' cols='110' rows='3' class='input'><?=$pc["notice5"]?>
</textarea>
                                      <textarea name='notice6' cols='110' rows='3' class='input'><?=$pc["notice6"]?>
</textarea>
                                      <textarea name='notice7' cols='110' rows='3' class='input'><?=$pc["notice7"]?>
</textarea>
                                      <textarea name='notice8' cols='110' rows='3' class='input'><?=$pc["notice8"]?>
</textarea>
                                      <textarea name='notice9' cols='110' rows='3' class='input'><?=$pc["notice9"]?>
</textarea>
                                      <textarea name='notice10' cols='110' rows='3' class='input'><?=$pc["notice10"]?>
</textarea>
                                      <textarea name='notice11' cols='110' rows='3' class='input'><?=$pc["notice11"]?>
</textarea>
                                      <textarea name='notice12' cols='110' rows='3' class='input'><?=$pc["notice12"]?>
</textarea>
                                      <textarea name='notice13' cols='110' rows='3' class='input'><?=$pc["notice13"]?>
</textarea>
                                      <textarea name='notice14' cols='110' rows='3' class='input'><?=$pc["notice14"]?>
</textarea>
                                      <textarea name='notice15' cols='110' rows='3' class='input'><?=$pc["notice15"]?>
</textarea>
                                      <textarea name='notice16' cols='110' rows='3' class='input'><?=$pc["notice16"]?>
</textarea>
                                      <textarea name='notice17' cols='110' rows='3' class='input'><?=$pc["notice17"]?>
</textarea>
                                      <textarea name='notice18' cols='110' rows='3' class='input'><?=$pc["notice18"]?>
</textarea>
                                      <textarea name='notice19' cols='110' rows='3' class='input'><?=$pc["notice19"]?>
</textarea>
                                      <textarea name='notice20' cols='110' rows='3' class='input'><?=$pc["notice20"]?>
</textarea>
                                      <textarea name='notice21' cols='110' rows='3' class='input'><?=$pc["notice21"]?>
</textarea>
                                      <textarea name='notice22' cols='110' rows='3' class='input'><?=$pc["notice22"]?>
</textarea>
                                      <textarea name='notice23' cols='110' rows='3' class='input'><?=$pc["notice23"]?>
</textarea>
                                      <textarea name='notice24' cols='110' rows='3' class='input'><?=$pc["notice24"]?>
</textarea>
                                      <textarea name='notice25' cols='110' rows='3' class='input'><?=$pc["notice25"]?>
</textarea>
                                      <textarea name='notice26' cols='110' rows='3' class='input'><?=$pc["notice26"]?>
</textarea>
                                      <textarea name='notice27' cols='110' rows='3' class='input'><?=$pc["notice27"]?>
</textarea>
                                      <textarea name='notice28' cols='110' rows='3' class='input'><?=$pc["notice28"]?>
</textarea>
                                      <textarea name='notice29' cols='110' rows='3' class='input'><?=$pc["notice29"]?>
</textarea>
                                      <textarea name='notice30' cols='110' rows='3' class='input'><?=$pc["notice30"]?>
</textarea>
                                      <textarea name='notice31' cols='110' rows='3' class='input'><?=$pc["notice31"]?>
</textarea>
                                      <textarea name='notice32' cols='110' rows='3' class='input'><?=$pc["notice32"]?>
</textarea>
                                      <textarea name='notice33' cols='110' rows='3' class='input'><?=$pc["notice33"]?>
</textarea>
                                      <textarea name='notice34' cols='110' rows='3' class='input'><?=$pc["notice34"]?>
</textarea>
                                      <textarea name='notice35' cols='110' rows='3' class='input'><?=$pc["notice35"]?>
</textarea>
                                      <textarea name='notice36' cols='110' rows='3' class='input'><?=$pc["notice36"]?>
</textarea>
                                      <textarea name='notice37' cols='110' rows='3' class='input'><?=$pc["notice37"]?>
</textarea>
                                      <textarea name='notice38' cols='110' rows='3' class='input'><?=$pc["notice38"]?>
</textarea>
                                      <textarea name='notice39' cols='110' rows='3' class='input'><?=$pc["notice39"]?>
</textarea>
                                      <textarea name='notice40' cols='110' rows='3' class='input'><?=$pc["notice40"]?>
</textarea>
                                      <textarea name='notice41' cols='110' rows='3' class='input'><?=$pc["notice41"]?>
</textarea>
                                      <textarea name='notice42' cols='110' rows='3' class='input'><?=$pc["notice42"]?>
</textarea>
                                      <textarea name='notice43' cols='110' rows='3' class='input'><?=$pc["notice43"]?>
</textarea>
                                      <textarea name='notice44' cols='110' rows='3' class='input'><?=$pc["notice44"]?>
</textarea>
                                      <textarea name='notice45' cols='110' rows='3' class='input'><?=$pc["notice45"]?>
</textarea>
                                      <textarea name='notice46' cols='110' rows='3' class='input'><?=$pc["notice46"]?>
</textarea>
                                      <textarea name='notice47' cols='110' rows='3' class='input'><?=$pc["notice47"]?>
</textarea>
                                      <textarea name='notice48' cols='110' rows='3' class='input'><?=$pc["notice48"]?>
</textarea>
                                      <textarea name='notice49' cols='110' rows='3' class='input'><?=$pc["notice49"]?>
</textarea>
                                      <textarea name='notice50' cols='110' rows='3' class='input'><?=$pc["notice50"]?>
</textarea>
                                     
                                      <input type="submit" name="submit" value="Click here to update" class="input">
                                    </form>

</body>
</html>