[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MySQL Server

47K

Solutions

23K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi all

I have a table of over 1000 users where their images are currently on the file system. The new solution requires me to move these to BLOBs in the table (they are only about 10k each) but I wondered how to batch do this.

The file system has the image in forename_surname.jpg format and I also have both of those in the table as eForname and eSurname, I also have the users employee id as eID. I guess my question is how can I loop the entire table and build the insert please?


This is my single update that's hard coded:

UPDATE directory.employee
SET ePhoto = LOAD_FILE("C:\\Apache24\\htdocs\\directory\\uploads\\Aaron_Bull.jpg")
AND eID = 1;

I've tried this and it doesn't work, having said that's im completely winging it :)
SELECT @i:=eID, @u:=CONCAT_WS("_",eForename,eSurname) 
(UPDATE directory.employee     
SET ePhoto = LOAD_FILE("C:\\Apache24\\htdocs\\directory\\uploads\\@u.jpg")
WHERE eID = @i))
FROM directory.employee

Open in new window


This however does return all the names with a _ between them and the assigned values of @u and @i so I think I'm somewhere on the right track

SELECT @i:=eID, @u:=CONCAT_WS("_",eForename,eSurname) as name, @u, @i FROM directory.employee;

Open in new window


Thanks in advance, Neil
0
hi,

As our test platform do not have internet access to let yum install download all rpm and dependency, we are check if it is possible for us to add internet access for all test machine for us to download MySQL cluster binary and install it successfully with yum install, what is the URL for Yum to work ?

repo.mysql.com

?
0
I have multiple users rows with a password that is plain text. I just want to run a loop in the database to hash all those passwords.

So, In my model I would just want to do something like this:

      
	$this->db->query("SELECT `user_password` FROM `TABLE 23`");
		$results = $this->db->resultSet();
		$db_pass = $results->user_pass;
		$passHash = password_hash($db_pass, PASSWORD_BCRYPT, [12]);
		
		$this->db->query("UPDATE `TABLE 23` SET `user_password` = :user_password");
		$this->db->bind(":user_password", $passHash);
		if($this->db->execute()) {
			return true;
		} else {
			return false;
		}

Open in new window


But I am getting an error:

Trying to get property of non-object

The line in question is:

$db_pass = $results->user_pass;

Open in new window


I think the issue is again how the data is being sent back i.e.: an array of objects.
0
I built a LInux 2 Amazon Apache 2.4 instance that uses php-fpm.  I have had several segfaults lately and then the server crashes.  I installed atop and noticed when the server crashed I had several php-fpm pools open.  At 1 PM today I had 1.4 GB free memory and now I have 900 mb free memory.

Something seems to be wrong with php-fpm because when I restart it the free memory jumps back up to 1.3 GB.

Here is my current atop screenshot - https://gyazo.com/5028ec3f184ff973409d6cba079d3767 you can see the last column shows only a small percentage of the processes are using memory.  Yet I keep running out of memory does anyone have any suggestions?  I have atop and sar logging.
0
I am trying to COUNT the total number of vouchers and then COUNT the number of vouchers  have a date of  0000-00-00 in the same query. Then I can send the data back to the controller and do some maths with the totals.

SELECT COUNT(`voucher_id`) as `totalVouchers`, `sp_name`, `prod_id`, `prod_name`, `edition`, `frontend`, `voucher_id`
		FROM `bb_products` as `bbp`
		INNER JOIN `service_providers` as `sp` ON sp.`service_provider_id` = bbp.`provider_id`
		INNER JOIN (SELECT `voucher_id`, COUNT(`voucher_id`) AS `vouchersBought` FROM `vouchers` WHERE `purchased` = 0000-00-00) as `v` on v.`product_id` = bbp.`prod_id`
		GROUP BY bbp.`prod_id`
		ORDER BY `sp_name` ASC

Open in new window

This code is not cutting it. The error is:

Column not found: 1054 Unknown column 'edition' in 'field list

Open in new window

1
I have created the system DSN and as well User DSN on my system control panel successfully by ODBC Connector  against MySQL 8 server DB. now i want to use this this database on my ColdFusion server and for this i am creating the Data-source on my ColdFusion server. But there showing errors . Please guide me that how can i use this database or DSN on my ColdFusion server and what type of driver i have to select in ColdFusion administration for creating Data-source .

Thanks In advance!
0
I need a way to see the dimension of the Woo Commerce product images laid out in a table in this manner. The table would have 4 columns - file name / width / height / link to wordpress media page. And the table would be in order of image size from small to large.

This could be a mysql query or a php program.  Hopefully, tied into a plugin in my wordpress installation. A plugin would be best because I will be referring to this on a regular basis.

I am running php 7.2 and a Maria 10 db.

Thanks,
0
I have a MySql table with values that look like the example below (in the first column). I am looking for an SQL update script that populates a different column for each value by converting each value as in the example below.

The converted value always has 14 characters

1. first 4 characters are the first 4 of the value, trailed by 0s to make sure there are always 4 characters. Example: A becomes A000, A01 becomes A010, A01B remains as is.
2. second 4 characters are the characters after first 4 in the values below and before the "/" sign , with 0 preceeding in case there are less than 4 characters before the "/" sign
3. last 6 characters are the characters after the "/" sign, tailed by 0s to ensure there are exactly 6 characters in this subset

Example
A ->               A0000000000000
A01 ->          A0100000000000
A01B ->       A01B0000000000
A01B1/00->A01B0001000000
A01B1/02->A01B0001020000
A01B1/022->A01B0001022000
A01B1/024->A01B0001024000
A01B12/04->A01B0012040000

Thank you, experts!
0
How can I strip off unprintable characters in a MySQL table when reading from php.

php generates this code:
<div class="col-sm-6 col-xs-6" style="padding-top:10px; font-size:20px"><select name="cfr">
			<option value="FRYSAFE-0020-1-S4/M" >FRYSAFE-0020-1-S4/M -$48,955</option>
			<option value="FRYSAFE-0035-1-S4/M" >FRYSAFE-0035-1-S4/M -$49,445</option>
			<option value="FRYSAFE-0050-1-S4/M�" >FRYSAFE-0050-1-S4/M� -$51,940</option>
			<option value="FRYSAFE-0075-1-S4/M" >FRYSAFE-0075-1-S4/M -$52,545</option>
			<option value="FRYSAFE-0075-3-S4/M" >FRYSAFE-0075-3-S4/M -$73,445</option>
			<option value="FRYSAFE-0110-1-S4/M�" >FRYSAFE-0110-1-S4/M� -$55,370</option>
			<option value="FRYSAFE-0110-3-S4/M�" >FRYSAFE-0110-3-S4/M� -$76,160</option>
			<option value="FRYSAFE-0165-3-S4/M�" >FRYSAFE-0165-3-S4/M� -$76,270</option>
			<option value="FRYSAFE-0250-3-S4/M" >FRYSAFE-0250-3-S4/M -$80,350</option>
			<option value="FRYSAFE-0360-3-S4/M" >FRYSAFE-0360-3-S4/M -$81,205</option>
			<option value="FRYSAFE-0565-3-S4/M" selected>FRYSAFE-0565-3-S4/M -$87,465</option>
			<option value="FRYSAFE-0815-3-S4/M" >FRYSAFE-0815-3-S4/M -$89,030</option>
			<option value="FRYSAFE-1450-4-S4/M" >FRYSAFE-1450-4-S4/M -$103,890</option>
			<option value="FRYSAFE-2300-4-S4/M�" >FRYSAFE-2300-4-S4/M� -$122,780</option>
		</select>
	</div>

Open in new window


I want to get rid of the ? in the black box.

Thanks
0
Hi Experts

Could you point how to represent a multiselect combo (options choosed/ not choosed) by using Smarty PHP library?

// The array area[] is correctly received by the view
//Depending on its values the multiselect combo options are choosed or not.

//Our try was 
<select id=area name='areaas[]'  tabindex="2" class= "small" multiple=""   >
<option value=1 {echo in_array('1', $areas) ? 'selected' : ''; }>Manufatura</option>
<option value=2 {echo in_array('2', $areas) ? 'selected' : ''; }>EHS</option>
...
</select>

//That causes a Smarty error

Open in new window


Could you suggest how to correctly obtain it by using Smarty?

Thanks in advance
0
Hi

I have a metadata table similar to bellow
wp_postmeta			
meta_id	post_id		meta_key	meta_value
222		1		country		UK
456		2		country		US
876		3		country		UK,US
26667	4		country		UNITED KINGDOM
47489	5		country		United Kingdom
123456	6		country		United States of America
56789	7		country		UNITED KINGDOM,United States of America

Open in new window


I need to find all the rows that do not have 2 figure country code I thought about using the command bellow  but row 3 is valid

SELECT meta_id, meta_value 
FROM wp_postmeta
WHERE
meta_key =  'country' AND LENGTH(meta_value) >2;

Open in new window


Any ideas how to do this so I return post_id 4,5,6 & 7?
0
I need to export mysql data to excel in csv format with php. I have used composer to install phpspreadhsheet  and I created a class in my MVC project which instantiates it etc. I also got it to work with the basic demo but that manually puts data into cells. I want to get the data from a mysql database using a normal SQL query like I would use just to output the results onto a web page. This is the code:

// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hello')
    ->setCellValue('B2', 'world!')
    ->setCellValue('C1', 'Hello')
    ->setCellValue('D2', 'world!');

// Miscellaneous glyphs, UTF-8
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A4', 'Miscellaneous glyphs')
    ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

Open in new window


I have no idea how to do this and I don't see any suggestions in the documentation (that I could find).

I really don't have to use this, I just need any way (the simpler the better) of exporting mysql data to excel. But even though I need it to be .csv, I still want the data organized in columns and not just a long row of data separated by commas.
0
Hello guys I have some question how can I achieve to Restore database using ajax post method. I can't send $dbname variable into myphp-restore.php.
Here is my sample code.
restore.php
<?php
$files = scandir('backup');
sort($files); // this does the sorting
foreach($files as $file){
	if($file != '.' && $file != '..' && !is_dir($file)){
echo '<form method="post" action="">
	<input type="text" name="dbname" value="'.$file.'" />
	<input type="submit" name="submit" value="Restore Backup" />
	</form>
';
	}
}
?>
<div class="div1" id="div1"></div>
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
    <script>
      $(function () {

        $('form').on('submit', function (e) {

          e.preventDefault();

          $.ajax({
            type: 'post',
            url: 'myphp-restore.php',
            data: $('form').serialize(),
            success: function () {
              $("#div1").load("myphp-restore.php");
            }
          });

        });

      });
    </script>

Open in new window

myphp-restore.php
<?php 
$dbnames = $_POST["dbname"];
define("DB_USER", 'root');
define("DB_PASSWORD", '');
define("DB_NAME", 'root');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", 'backup');
define("BACKUP_FILE", $dbnames);
define("CHARSET", 'utf8');

class Restore_Database {
    var $host;
    var $username;
    var $passwd;
    var $dbName;
    var $charset;
    var $conn;
    function __construct($host, $username, $passwd, $dbName, $charset = 

Open in new window

0
Hi Experts

Could you point the reason this object returned by Codeigniter's model doesn't actualize the multiselect comboboxes on the view?

Accordingly to:
// Array returned by the CI model
stdClass Object ( [0] => stdClass Object ( 
	 [id] => 1 [nome_startup] => startup001 [slug] => startup001.com [id_coworking] => 1 [descricao] => Startup de teste1111111111 [site] => xxx.cccc.com.br [active] => 1 [areas] => Array ( [1] => 1 [2] => 2 ) [tipos] => Array ( [1] => 1 [2] => 4 ) )

Open in new window


//View's code
// This part that has the multiselect combo isn't correctly actualized with the content from the arrays "areas" and "tipos" (all the other values are correctly actualized with the array's content)
 <tr style="height:25px">

	<td width="20%" class="dvtCellLabel" align="right">Área de Atuação:</td>
	<td align="left" class="dvtCellInfo">
		<select id="_area" name="areas[]" tabindex="2" class="small" multiple="">  
			<option value=1>Manufatura</option>                                                     
			<option value=2>EHS</option>
			<option value=3>Logística/ Supply-chain</option>
			<option value=4>Marketing</option>

		</select>
	</td>
	
	
	<td width="20%" class="dvtCellLabel" align="right">Tipo de Solução:</td>
	<td align="left" class="dvtCellInfo">
		<select id="tipos" name="tipos[]" tabindex="2" class="small" multiple="">                                           
			<option value=1>Workflow management</option>                                                     
			<option value=2>Manufacturing Process Management</option>
			<option value=3>Robotic Process Automation</option>
			<option value=4>Realidade Virtual Aumentada</option>
		
		</select>
	</td>

</tr>

Open in new window


Could you point what must to be adjusted?

Thanks in advance!
0
I have over 2,000 images in a Woo Commerce installation.  I need to know where the image dimensions are stored on the Maria db for the single product image or main image that Woo Commerce uses for each product and how to retrieve the list of them.

I plan to replace all of the images that aren't at least 300x300.  Because on the cart they are showing up blurry.

Doing it through the db will be much quicker than going through each product on the cart.

Thanks,
0
This is a follow-on to the question.  
https://www.experts-exchange.com/questions/29109489/Need-Woocommerce-Category-Sorted-by-Description-Within-Product.html

Open in new window


The plugin works great for the sale category but it seems to be making all of the categories behave in the same manner.  Is it possible, to just specify in the plugin to use its logic just for the sale category?
0
We have to serve 50,000,000,000 requests per day, our app can handle that, but we're not sure if our database skills are there to pick this up. We have a database server with 256gb of ram and 512GB SSD RAID using percona edition. Some of the data needs to stay on the database for 30 days, the transaction table where all requests are stored. what approach do we need to scale this horizontally? each endpoint requests runs about 4 queries...
0
Hi Experts

Could you point a way to transverse a PHP array and transform a string with a separator  "|" to transform it as an separate array?

Accordingly to:
// The array content

 [2] => stdClass Object ( [id] => 7 [nome_startup] => startup004 [slug] => startup004 [id_coworking] => 1 [descricao] => startup004 [site] => startup004 [active] => 1 [areas] => |1|2| [tipos] => |3|4| ) )


// The indexes [areas]  and [tipos]  have string contents    |1|2|   and  |3|4|

What I need is to obtain 2 separated arrays containing the respective values (1 and 2)  - (3 and 4) 


// My try to do that

       $areas=array();
       $tipos=array();
	
	
	foreach($x as $key=>$value) 
	{
		  if($key == 'areas')
		  {
			foreach($value as $key=>$value2)
			{
				$value2 = str_replace("|", "", $value2);
				array_push($areas, $value2);
			}
		  }
		 else
		 {
			if($key == 'tipos')
			{
				foreach($value as $key=>$value2)
				{
					$value2 = str_replace("|", "", $value2);
					array_push($tipos, $value2);

				}
			} 
		 }
	 } 

Open in new window


Thanks in advance.
0
hi,

I am setting up the first MySQL cluster and I need to install the auto install rpm first, but that one said it depends on community server rpm first, which asking for the client to be install, but when I install the client it complain:

when installing MySQL client
so how can I get ride of that error when installing the client?

what is perl (Class::MethodMaker) ? how to install it?

I download all component from https://dev.mysql.com/downloads/cluster/

and now the files list is:

MySQL  Cluster binary downloaded.
anything missed?
0
Hi,

Looking for advice on how best to change the background colour of a cell's table row based upon it's contents.

This is the basic code: -

<div><h5>Process Check - Total No. of Units which Failed to Transfer (Last Addition)</h5></div>
	<table id="table" class="table table-bordered table-striped table-hover">  
				<thead>
					<tr>
						<th class="success text-left">Date of Deduction</th>
						<th class="success text-left">Total No. of Units</th>
					</tr>
				</thead>
				<tbody class="searchable">

	<?php
	
	$query = "select timestamp, totalAssemblyUnits from sumAssemblyUnitsGoodsIn2 order by timestamp desc limit 1";

	if (!$result = $mysqli->query($query)) {
		$err
		= "QUERY FAIL: "
		. $query
		. ' ERRNO: '
		. $mysqli->errno
		. ' ERROR: '
		. $mysqli->error
		;
		trigger_error($err, E_USER_ERROR);
		}

		while ($row = $result->fetch_object()) {
		$tr = <<<EOD
				<tr>
					<td> $row->timestamp </td>
					<td> $row->totalAssemblyUnits </td>
				</tr> 
EOD;
		echo $tr;}

	?>
	</table>

Open in new window


If the mySql Output for "<td> $row->totalAssemblyUnits </td>" is ever less than zero, then I like to colour the background of the cell red, otherwise it should be coloured green.

Hope that makes sense?

J
0
I am trying to check my entire cart array against the database when a user checkouts to make sure that they items in their cart are still actually available and another user didn't purchase the last of an item somewhere in between. But I am having a hard time trying to figure out getting more than one record. Before I even get to checking what is in the cart vs the database, I am just trying to get the quantities in the database for each product id at this stage.

Here is my controller that is just putting the data from the database call into the $totals variable.

	public function checkoutDbQtyCheck()
	{
		if(isset($_SESSION['cart_array'])) {
			
			$response = array();
			
			$totals = $this->ProductFendModel->finalDbQtyCheckoutCheck();
			
			$response['cart'] = $totals;
			echo json_encode($response);
		
		}
	}

Open in new window


This is the database query in the model:

	public function finalDbQtyCheckoutCheck()
	{
		$purchased = '0000-00-00';
		$this->db->query("SELECT COUNT(`product_id`) as `vouchersLeft`, `product_id` FROM `vouchers` WHERE `product_id` = :product_id AND `purchased` = :purchased");
		$this->db->bind(":purchased", $purchased);
		$this->db->bind(":product_id", $prod_id);
		
		foreach($_SESSION['cart_array'] as $cart) {
			$prod_id = $cart['prod_id'];
			$quantity = $cart['quantity'];
		}
		
		$results = $this->db->resultSet();
		return $results;
	
	}

Open in new window


Whether I put $results in the foreach loop or where it is now, I only get back one result even though there are multiple items in the cart.
0
Hi Experts

Could you point a way to code a multichoices combo by using "Smarty" PHP library?

Thanks in advance!
0
This is a follow on to this question - https://www.experts-exchange.com/questions/29107724/Need-Category-Sorted-By-Product-Not-First-Letter.html#acceptAnswerByMember

Need to have category page sorted by product and within by description.  Putting $off, %off, buy x.  With the x amount being in an ascending sequence.

For example, Buy 4 then Buy 5 then Buy 9 etc.
0
Hi Experts

Could you point a strategy to call a new CRUD view to complete an existent register?

Accordingly with:

img002
The basic data is still registered,

My first attempt is to put a button to call a view and then enter the Address informations (and then communications info maybe by another button)

Is it a good strategy?  or possibly you could suggest anything else.

I'm using pure PHP in a custom MVC project (no PHP frameworks used).

Thanks in advance!
0
How to write php output to excel? more than 100 rows and each 10 column. Platform linux.
0

MySQL Server

47K

Solutions

23K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.