lindm
-
Posts
199 -
Joined
-
Last visited
Never
Posts posted by lindm
-
-
Why does
SHOW COLUMNS FROM table LIKE 'cx%'
work, but not
SHOW COLUMNS FROM table,table2 LIKE 'cx%'
?
-
With help from the board the much appreciated script below was written to update all columns of a mysql database with the corresponding $_POST value. It also had the option to leave out columns from this update. This script was used with one single table. Now I have a three table design of my mysql database and of course have trouble since to my understanding an update of multiple selected tables must be in the form of:
SET table1.fieldname1 ='1', table2.fieldname2='2'etc while the code below produces SET fieldname1='1', fieldname2='2' etc
Thow code below doesn't adjust for this and simply adding all tables to the UPDATE query as follows does not work:
$q = 'UPDATE `table1, table2, table3` SET '. implode( ', ', $qArray ) .' WHERE `userName` = \''. $user2 .'\'';
Is it possible to adjust this code for updating multiple tables or is it impossible?
Original script
if ( count($_POST) > 0 ) { # The names of any elements you don't want # added to mysql... most importantly the # submit button If you put the fields you # want added in their own array, there's no # need for this $doNotAdd = array( 'column1','column2','column3' ); # Create the array to store each query row $qArray = array(); # Loop through post data foreach ( $_POST as $key => $val ) # Make sure key isn't in 'doNotAdd' list if ( !in_array( $key, $doNotAdd ) ) $qArray[] = '`'. $key .'` = \''. mysql_real_escape_string( $val ) .'\''; # Verify there's actually data to insert if ( count($qArray) > 0 ) { # Build query $q = 'UPDATE `table` SET '. implode( ', ', $qArray ) .' WHERE `userName` = \''. $user2 .'\''; # Debug - incase stuff goes wrong remove # below # echo $q; # Check if query goes through if ( !mysql_query($q) ) # Query broked! echo 'Could not update set...'; } } } -
-
Works fine. Still annoying why the other code won't work...any guesses?
-
Have a small anomaly which is driving me crazy.
The page is pretty much self-explaining...simply load in a browser or check: http://www.drlindmark.se/kevin/script.html
<head> <style type="text/css" media="screen,print"> .checked {background-color: #F1F1F1;} </style> <script type="text/javascript"> function pl(name) { if (name.checked == true) {name.parentNode.parentNode.className = "checked";} else {name.parentNode.parentNode.className = "xxx";} } function plt(name) { if (name.checked == true) {name.parentNode.parentNode.parentNode.className = "checked";} else {name.parentNode.parentNode.parentNode.className = "xxx";} } </script> </head> <body> <form name="form1" method="post" action=""> <table border="1" cellspacing="0" cellpadding="0" class=""> <tr> <td width="355">Tableclass 1 - not checked and no class. Works fine.</td> <td width="120"> </td> <td width="120"> </td> <td width="25"> <input type="checkbox" name="checkbox" id="checkbox" onclick="plt(this)"> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td> </td> </tr> </table> <br> <table border="1" cellspacing="0" cellpadding="0" class="checked"> <tr> <td width="355">Tableclass 2 - checked and class set.</td> <td width="120"> </td> <td width="120"> </td> <td width="25"> <input name="checkbox" type="checkbox" id="checkbox" onclick="plt(this)" checked> </td> </tr> <tr> <td><strong>PROBLEM!</strong> Why does not the background change??</td> <td> </td> <td> </td> <td> </td> </tr> </table> <br> <table border="1" cellspacing="0" cellpadding="0"> <tr class=""> <td width="355">Rowclass 1 - not checked and no class. Works fine.</td> <td width="120"> </td> <td width="120"> </td> <td width="25"> <input type="checkbox" name="checkbox2" id="checkbox2" onclick="pl(this)"> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td> </td> </tr> </table> <br> <table border="1" cellspacing="0" cellpadding="0"> <tr class="checked"> <td width="355">Row class 2 - checked and class set. Works fine.</td> <td width="120"> </td> <td width="120"> </td> <td width="25"> <input name="checkbox2" type="checkbox" id="checkbox2" onclick="pl(this)" checked> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td> </td> </tr> </table> <br> </form> </body> </html> -
Solved it
<?php include ('mysqlconnnection.php'); //include connection //gets all data $resultCY = mysql_query("SELECT * FROM $table") or die("mysql error"); //retrieves columns ending with cy $rowCY = mysql_fetch_array($resultCY); //find all columns ending with py $resultPY = mysql_query("SHOW COLUMNS FROM $table LIKE '%py'") or die("mysql error"); //retrieves columns ending with py $numColumns = mysql_num_rows($resultPY); //gets number of rows ending with PY //creates an array where columns XXXXpy=value of XXXXcy. STUCK here. $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($resultPY); $col[$colname[0]] = $colname[0]."='".$rowCY[str_replace("py", "cy", $colname[0])]."'"; $x++; } //print_r($col); $query = 'UPDATE '.$table.' SET '. implode( ', ', $col ) ; //new array is used in mysql query //echo $query; //exit; mysql_query($query); ?> -
Corrected the array now. Silly fault. Array now is for instance:
Array ( [RRintNOpy] => 100000 [RRintFORLAGpy] => 0 [RRintAKTARBpy] => 0)
Now just need to create the mysql_query...
<?php include ('mysqlconnnection.php'); //include connection //gets all data $resultCY = mysql_query("SELECT * FROM $table") or die("mysql error"); //retrieves columns ending with cy $rowCY = mysql_fetch_array($resultCY); //find all columns ending with py $resultPY = mysql_query("SHOW COLUMNS FROM $table LIKE '%py'") or die("mysql error"); //retrieves columns ending with py $numColumns = mysql_num_rows($resultPY); //gets number of rows ending with PY //creates an array where columns XXXXpy=value of XXXXcy. STUCK here. $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($resultPY); $col[$colname[0]] = $rowCY[str_replace("py", "cy", $colname[0])]; $x++; } $querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\''; //new array is used in mysql query mysql_query($querycx); ?> -
Here is my current status. Stuck on the array part. Any help appreciated:
<?php include ('mysqlconnnection.php'); //include connection //gets all data $resultCY = mysql_query("SELECT * FROM $table") or die("mysql error"); //retrieves columns ending with cy $rowCY = mysql_fetch_array($resultCY); //find all columns ending with py $resultPY = mysql_query("SHOW COLUMNS FROM $table LIKE '%py'") or die("mysql error"); //retrieves columns ending with py $numColumns = mysql_num_rows($resultPY); //gets number of rows ending with PY //creates an array where columns XXXXpy=value of XXXXcy. STUCK here. $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($resultPY); $col[$colname[0]] = str_replace("py", "cy", $row[$colname[0]]); $x++; } $querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\''; //new array is used in mysql query mysql_query($querycx); ?> -
I need help with a script that performs the following:
1. Moves the contents from columns %cy to columns %py
Basic starting points:
1. Table contains different columns but those ending with %cy have a "pair column" ending with %py
-
I want to show columns in a table ending with cy and py. Is this possible?
Suggestion that does not work:
SHOW COLUMNS FROM $table LIKE '%cy' or LIKE '%py'
-
Solved it now. Used array_diff to create a new array:
<?php include ('mysqlconnnection.php'); //include connection $arrExclude = array('userId', 'userName', 'userPass', 'firma', 'orgnr', 'Scroll'); $result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error"); $numColumns = mysql_num_rows($result); $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($result); if(!in_array($colname, $arrExclude)) { $col[$colname[0]] = $colname[0]; } $x++; } $col2= array_diff($col,$arrExclude); //removes arrExclude from the created array $col $querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col2 ) .'=\'\''; //new array is used in mysql query mysql_query($querycx); ?>Thanks for all help!
-
Hi samshel,
The code executes now but the excluded columns are still included in the query, strange. If you see any obvious solution please let me know. Here is the current code (echo query instead of mysql_query until the code is ready).
<?php include ('mysqlconnnection.php'); //include connection $arrExclude = array('id', 'userName', 'userPass', 'firma', 'orgnr', 'Scroll'); $result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error"); $numColumns = mysql_num_rows($result); $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($result); if(!in_array($colname, $arrExclude)) { $col[$colname[0]] = $colname[0]; } $x++; } $querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\''; echo $querycx; ?> -
Get an error
Parse error: syntax error, unexpected '{' in /Library/WebServer/Documents/clear.php on line 16Current script
<?php include ('mysqlconnnection.php'); //include connection $arrExclude = array('id', 'userName', 'userPass', 'firma', 'orgnr', 'Scroll'); $result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error"); $numColumns = mysql_num_rows($result); $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($result); if(!in_array($colname, $arrExclude) { $col[$colname[0]] = $colname[0]; } $x++; } $querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\' WHERE `userName` = \''. $user2 .'\''; mysql_query($querycx); ?> -
I have this query to set all columns of a table to nothing. Now I want to exclude some of the columns from the query. How to?
<?php include ('mysqlconnnection.php'); //include connection $result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error"); $numColumns = mysql_num_rows($result); $x = 0; while ($x < $numColumns) { $colname = mysql_fetch_row($result); $col[$colname[0]] = $colname[0]; $x++; } $querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\' WHERE `userName` = \''. $user2 .'\''; echo $querycx; ?> -
Revraz,
Please specify problems. Will queries stop in the middle, will data be lost?
I basically want to do 2 queries:
1. SELECT * from table with echo $row[columname]
2. UPDATE table
-
Response to Barand,
There is a fixed input field in a html form for each mysql column. For instance the contents of input field with id/name Debt1 will be recorded to column Debt1 in the mysql database when the user presses Save.
-
Much appreciate your input Fenway.
So I will probably have some table with 500 columns...are there any risks or problems with this large amount of columns?
Is there any way to make the setup more ideal?
Should I perhaps consider other database solutions?
-
Only one really large form which is the same for each user. The user enters data into the form which forms the database entry. All data (form field input) from the form is unique for every user.
-
1...N mainly represent unique form elements such as text fields, text areas as well as some checkboxes. The user will enter numbers or text into the form elements press save to save each element to the database.
So each user will have a set amount of unique form elements/mysql columns.
-
Fenway,
Hope you don't mean one table per field??
Perhaps the following setup is better?
Table User : Contains user data, approx 5 columns
Table Settings : Contains user settings, approx 30 columns
Table Income : The income statemetnt, approx 100 columns
Table BalanceAsset: Balance sheet Assets, approx 100 columns
Table BalanceDebt: Balance sheet Debts, approx 100 columns
etc
etc
-
Hello Barand,
Understand your point. For a non-native english speaking person and a basic skilled mysql user the article has somewhat complicated text:
I have read the article but can mainly see one "normalization" issue: the company data and the financial report data. The fact still remains that I will have approx 2500 columns of "spreadsheet data" each company. Is splitting the financial report in different tables (ie one for settings, one for income statement, one for balance sheet, one for notes etc) really necessary? Each column will mainly contain figures not exceeding a 6-figure amount. Each column of the financial statement is unique for the company and not like an "author-column" in the article example.
Where ever I read the main idea is that mysql databases over 30-60 columns is not designed well. I will never come down to this figure...
My basic question is if a mysql database with 2500 columns containing unique very little data will cause problems? What type of problems (the article mentions "data redundancies, data anomalies, and various inefficiencies", please help me understand)?
Thank you
-
I have some little experience of mysql and php and am planning a project that could involve 2500 columns of a database. The project is nothing fancy but a financial report for companies. I am however not sure this is such a good idea or even possible?? Would much appreciate advice on the matter.
For you to get the idea, the table would basically be set up with the following columns:
ID
Password
Company
Setting1
...
Setting50
Turnover
...
ProfitLossforTheyear
Asset1
...
Asset100
TotalAssets
Equity
...
Debt1
...
Debt100
TotalDebt
Notedata1
...
Notedata1500
-
I am making a budget service for my family members with different cost categories. Each column is the figure (cost) of a cost category. Is mysql not a good solution for this?
-
I have around 100 columns in my table. I want to create a script to reset approx. 90 of these. Instead of executing a long manual query in php I want to generate this on the go (in case I add new columns later on). So 10 of these are not to be "touched".
Hope it gets a bit clearer.
SHOW columns from multiple tables not possible?
in MySQL Help
Posted
No luck on that one.