Adding user in mysql – Database Administrators Stack Exchange

Why i’m not able to add user with privileges in mysql. i’m running command grant all privileges on *.* to 'username'@localhost identified by 'strong password';. i’m keep getting error ERROR 1064 (42000): 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 'identified by 'strong password'' at line 1. I can add user separately and then assign him the privileges on db but i like to add user and assign privileges in single command. I’m running following mysql version

| version                  | 8.0.23-0ubuntu0.20.04.1       |
| version_comment          | (Ubuntu)                      |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |

mysql – Is it possible to get a collection (array or object) from a SQL query containing a set of filters?

I’m used to other technos, I want to retrieve some info from a database, so to give the context:
I have a “plaformname” column which is the name of each platform, a “populationname” column (each platform is divided in populations) and finally I have a “nb_of_actions_completed_by_users” that contains a count.

I would like to retrieve the “nb_of_actions_completed_by_users” grouped as something like (not picky if it is on arrays, objects, mix, etc, I’m fine with any structure):

({
  "plaformname": "platform1",
  "populations": ({
    "populationA": 8, //this is the nb of actions
    },
    "populationB": 0
  })
},
{
  "plaformname": "platform2",
  "populations": ({
      "popX": 0
    },
    { 
      "popY": 5
    },
    {
      "popZ": 5
    })
})

-or-

{
  "platform1": {
    "populationA": 8, //this is the nb of actions
    "populationB": 0,
  },
  "platform2": {
    "popX": 0,
    "popY": 5,
    "popZ": 5
  }
}

Or do I have to run an individual query for each platform? Thanks in advance for the help/observations

mysql – como enviar el valor de varias variables onchange varios selects por ajax a un archivo php para hacer un consulta y mostrar los datos en un select

es decir que varie y que cambie dependiendo de la seleccion de los selects si cambia uno que filtre la condicion que si cambia a otro que lo filtre y asi etc.. he intentado enviando las variables del valor onchange de los selects por ajax a un archivo php hacer la consulta y mostrar pero solo se cumple con una variable
este el codigo de la conexion

mysql = new mysqli(Constantes::HOST, Constantes::USER, Constantes::PASSWORD, $bd);
mysqli_set_charset($this->mysql, “utf8”);
return $this->mysql;

}

public function consulta($query)
{
$i = 0;
$contenedor = array();
$result = mysqli_query($this->mysql, $query) or die(“Error en la consulta: $query “.mysqli_error());

while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
$contenedor($i) = $row;
$i++;
}

return $contenedor;
}

public function actualizacion($query)
{
mysqli_query($this->mysql, $query) or die(“Error en la consulta: $query “.mysqli_error());
}

public function desconectarDB()
{
mysqli_close($this->mysql);
}

}

asi muestro los selects principales

Departamento

Seleccione
conectarBD();
$consulta = “SELECT id_departamento,departamento FROM departamentos”;
$rst1 = $conf->consulta($consulta);
for($i = 0; $i ‘.$rst1($i)(“departamento”).”;
}
$conf->desconectarDB();
?>

                </select>
                <br>

asi hago el siguiente select junto la funcion ajax para que carguen los municipios acorde al departamento
Municipio

                <select id="id_municipio" name="municipio" class="select-css" style="width:190px;">
                    <option value="0">Seleccione</option>
                </select>
                <!--funcion ajax para traer el municipio acorde al departamento-->
                <script>
                     $(document).ready(function() {
                    var municipio = $('#id_municipio');
                         $('#id_departamento').change(function() {
                             var id_departamento = $(this).val();
                            $.ajax({
                                   data: {
                                      id_departamento: id_departamento
                                },
                                    dataType: 'html',
                                   type: 'POST',
                                   url: '../../getmunicipio.php',
                               }).done(function(data) {
                                   municipio.html(data);
                               });
                          });
                       });
                </script>



 

php mysql create an entry into a field. (INSERT INTO) WHILE logged in currentuser/session

I did the php registration system and it works
https://www.tutorialrepublic.com/php-tutorial/php-mysql-login-system.php

but i created extra fields in the DB so the user can add some data to the database.

All search results are only to add to database from scratch, not from CURRENT SESSION USER. or they are just how to make php user reg systems but no further PHP CURRENT SESSION user interaction with DB

DB structure is just like on that site, i posted above, but created a URL field so a user can save their favorite URL. DB = **users > username > URL **
once i get that working im going to figure out how a user can append to the field and keep adding URLs

i came up with this.. .

if (isset($_SESSION('loggedin')) && $_SESSION('loggedin') == true) {
echo "<a href="file.php?save=yes>Add to Favorites</a>";} 

if(isset($_GET('save'))){    
     echo "got a save";
     userID = $_SESSION("username");
     echo $userID; //it does give the current username, meaning im logged in , session-start is on the header . 

     $URL = $_GET('URLforbookmark');
     $sql = "INSERT INTO users (URL, UserID)
     VALUES ('$URL', '$UserID')";
     
     if (mysqli_query($sql)) {
        echo "New record created successfully !";
     } else {
        echo "Error: " . $sql . "
" . mysqli_error($sql);
     }
     mysqli_close($sql);
}

Feedback Error reads:
Error: INSERT INTO users (test, UserID) VALUES (‘theusersfavorurl’, ”)

thank you !

mysql – Insert into table with polymorphic association in database

I want to design a database where some tables have multiple foreign keys.
I am looking for the best design approach.

I found out that this kind of problem is called “polymorphic association” in database. I found the Stack Overflow question Foreign Key to multiple tables and Nathan Skerl’s answer very helpful.

But according to the link above, if I want to insert a new user (not a new ticket, but a new user), what would be the insert statement based on the design?

First I insert into the party? How can I retrieve the id to insert into user or group table? Because it has identifying relationship with user and group.

mysql – Update table column based on another table values

I have two tables:
Table 1:

ID    TICKET_AGENT    ORIGINAL_ID 
1          0               5
2          0               3
3          0               1
4          0               2

And,
Table 2:

ID    TICKET_AGENT 
1          A               
2          C               
3          B               
4          B

Is there any scenario where I can use UPDATE to update Table 1.TICKET_AGENT field by searching through ORIGINAL_ID as the ID of Table 2 and if ORIGINAL_ID is found in table 2, update table 1 after with the corresponding TICKET_AGENT value?

UPDATE: ORIGINAL_ID points to ID in table 2

mysql – WordPress doesn’t save changes some times

I have an wordpress website and have a lots of user’s that write content in it. Sometimes when users edit or create new post and save it, after refresh it disappeared.

I have a backup system that mysqldump all database every 8. I don’t know that this may cause the problem or not.

converting from oracle to mysql

please i want to convert this codes from oracle to mysql (phpmyadmin) using only ( clauses like select, where, group by, having, order by , union, joint )

  1. SELECT LEVEL, EMPNO, ENAME, JOB, MGR FROM EMP
    CONNECT BY MGR = PRIOR EMPNO
    START WITH MGR IS NULL
    ORDER BY LEVEL;
  2. SELECT LEVEL, ENAME FROM EMP
    WHERE ENAME <> ‘JONES’
    CONNECT BY MGR = PRIOR EMPNO
    START WITH ENAME = ‘JONES’
    ORDER BY LEVEL;
  3. SELECT LEVEL, AVG(SAL) FROM EMP
    CONNECT BY MGR = PRIOR EMPNO
    START WITH MGR IS NULL
    GROUP BY LEVEL;
  4. SELECT ENAME FROM EMP
    WHERE ENAME <> ‘JONES’ AND ENAME <> ‘SCOTT’
    CONNECT BY MGR = PRIOR EMPNO
    START WITH ENAME = ‘JONES’;
  5. SELECT ENAME FROM EMP
    WHERE ENAME <> ‘JONES’
    CONNECT BY MGR = PRIOR EMPNO AND ENAME <> ‘SCOTT’
    START WITH ENAME = ‘JONES’

mysql – WordPress don’t sage changes some times

I have an wordpress website and have a lots of user’s that write content in it. Sometimes when users edit or create new post and save it, after refresh it disappeared.

I have a backup system that mysqldump all database every 8. I don’t know that this may cause the problem or not.

sql server – i want to answer some request using mysql (phpmyadmin)

please i want to answer on some requests using mysql My questions are : i want:
1_ the Hierarchical structure of employees (NAME, JOB, EMPNO, MGR). The employee at the top of the hierarchy does not have a manager
2_ List of employees who depend on JONES. Indicate their level (LEVEL) in the hierarchy.
3_ Average salary for each level of employee.
4_ List of employees who work for JONES, except SCOTT
5_List of employees who work for JONES, except SCOTT and those who work for SCOTT.
here are the details to use ,
ps:  clés primaires == primary key
clés étrangers == foreign key