mysql script in a command shell

Shell Programming and Scripting
1

Hi
can anyone help with my issue here. I new in linux and new in scripting. i was ask to do the following below and i`m getting errors. I managed to create the table and the database. Now i need to add the details in a shell to update the database.
-----------------------------------------------------------------------

#!/bin/bash

#script that ask for user's name, surname  dob and age

echo "Enter your name"
read name

echo "Enter your surname"
read surname

echo "Enter your dob"
read dob

echo "Enter your age"
read age

age()

{
    age="dob -1901"
}

mysql -hlocalhost -uroot -p 
create database johannesburg;
show databases;
use johannesburg;
show tables;
CREATE TABLE johannesburg1 (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100), surname VARCHAR(100), dob DATE, age VARCHAR (10),
PRIMARY KEY (id)
);
INSERT into johannesburg1 VALUES ( "$name", "$surname", "$dob", "$age" );
2

mysql -e is what you are looking for.

mysql -hlocahost -uroot  -e "
  create table;
  do something else;
  do one more thing;
"
3

since the db and table are already created the first mysql command will finish the job but needs to supply the root passwd. there's more to consider. if non root users are using this you may wish to make the script executable but not readable to everyone(711). if you're actually doing more than the mysql command below you may want to push all of the mysql commands and variables into an sql script and execute command #2 then remove the sql script and #3 basically does the same thing but inside the orig script. you may need to export the variables depending on your method.

mysql -h localhost -u root -prootpasswd -D johannesburg -e "insert into  johannesburg1 values ('$name','$surname','$dob','$age');"

mysql -h localhost -u root -prootpasswd -D johannesburg < script.sql

mysql -h localhost -u root -prootpasswd -D johannesburg << EOF
create database johannesburg;
CREATE TABLE johannesburg1 (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100), surname VARCHAR(100), dob DATE, age VARCHAR (10),
PRIMARY KEY (id)
);
INSERT into johannesburg1 VALUES ('$name','$surname','$dob','$age');
quit;
EOF
4

thank u "crimso" i managed to creat my database. Now when i try to create the tables i get the following error "ERROR 1136 (21S01) at line 7: Column count doesn't match value count at row 1" and the error refers to this line "name VARCHAR(100), surname VARCHAR(100), dob DATE, age VARCHAR (10),
PRIMARY KEY (id)" i`m also trying to work around this issue. Pls assist

---------- Post updated at 05:16 AM ---------- Previous update was at 03:07 AM ----------

#!/bin/bash

echo "Enter name"
read name
echo "Enter surname"
read surname
echo "Enter dob"
read dob (i`m not sure if this is the issue cause it keeps on referring to this line)
echo "Enter age"
read age

mysql -hlocalhost -uroot -p -D durban << EOF
#create database durban;
use durban;
#CREATE TABLE soweto (
#id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
#name VARCHAR(100), surname VARCHAR(100), dob DATE, age INTERGER (10),
#PRIMARY KEY (id)
#);
INSERT into soweto VALUES ('$name','$surname','$dob','$age');
quit;
EOF

eish now i`m getting the following error "ERROR 1136 (21S01) at line 8: Column count doesn't match value count at row 1"

5

Just try this query.

insert into soweto(name,surname,dob,age) values ('$name','$surname','$dob','$age');

or

insert into soweto values(' ','$name','$surname','$dob','$age');
6

thanks man i`m just having small issues to sort out but its all good.

7

actually the error you're getting is an sql error and not an error related to line 8(read dob) of your script. kannanatlinux's first suggestion should get you around that sql error....

8

thank u guys it came right